Had a first serious go today at building a database schema in Postgresql using pgadmin3. It’s a fine tool, although I suspect I might go faster writing CREATE TABLE statements by hand once I get used to Postgres’ flavour of SQL.

What I’m looking at right now is a variant on the table-of-triples approach to sparse and latently-structured metadata. In the table-of-triples approach, you represent the attributes of each item by asserting a series of subject-predicate-object triples with the item as the subject, e.g.:

subject     predicate    object
------------------------------------------------
dominic    likes             sausages
dominic    hates           mashed potato

In the approach I’m considering, you have one table containing predicate-object pairs, each of which has an auto-incrementing integer alias, and another table containing subject-alias(predicate-object) pairs, e.g.

property_id   predicate   object
----------------------------------------------------
1                     likes           sausages
2                     hates         mashed potato

subject    property_id
------------------------------------
dominic   1
dominic   2

The point of this is to eliminate some redundancy (assuming that certain predicate-object pairs occur frequently, e.g. lots of people like sausages, or have the surname “Smith”). It also allows us to split a search into two parts – first find the property_id for a given property, then find the subjects that have that property – which means that the results of the first part of the search can be cached, speeding up repeated queries with the same criteria.

This entry was posted on Wednesday, November 30th, 2005 at 11:49 pm and is filed under General, Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.