Properties Table

an approach to creating flexible ontologies using sql

property table

< CREATE TABLE `property` (
<   `subject` text,
<   `subject_id` int(11) DEFAULT NULL,
<   `relate_id` int(11) DEFAULT NULL,
<   `object` text,
<   `object_id` int(11) DEFAULT NULL,
<   `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
< ) 

Things you can say with this schema:

subject      id   relate object  id
'track'      2    'has'  'genre' 4
'Jazz'       8    'is_a' 'genre' 
'Industrial' 10   'is_a' 'genre' 
'genre'      NULL 'is_a' 'class' 

relations

1 isa
2 has_property
3 has_member

example records:

here's how to set the genre for a track:

subject subject_id relate  object  object_id
'track' track_id   'has_a' 'genre' genre_id

INSERT INTO property (subject,subject_id,relate_id,object,object_id,created)
              VALUES ('track',53      ,2        ,'genre',3         ,now()  );

INSERT INTO property (subject,subject_id,relate_id,object,object_id,created)
              VALUES ('track',53      ,2        ,'genre',4         ,now()  );