A friend has been asking me how to model some very complex data that's only somewhat relational, in a sql database. He's got some application writing and designing skills, but no database knowledge whatsoever.
Anyway, I thought the answer we came up with might be of use to others. This really isn't a new concept, but it's not a very well known one ... in other words I didn't invent this schema, I'm just pointing it out in case this can help anybody model very complex data. This is optimized for flexibility,
not speed.
The code below is for sql server 2005, but should be very easy to port to whatever database engine you use.
Code:
Create Table XmlClobs (
XmlDocument Text,
HashValue varBinary(36)
XmlID int identity primary key,
)
Create Table Objects (
Label varChar(200) not null,
ObjectID int identity primary key
)
Create Table Attributes (
AttribName varChar(100) not null,
ShortValue varChar(300),
XmlID int foreign key references XmlClobs,
AttributeID int primary key
)
Create Table ObjectAttributes (
ObjectID not null int foreign key references Objects,
AttributeID not null int foreign key references Attributes,
ObjAtLinkID int identity primary key
)
Create Table Concepts (
Label varChar(100) not null,
Description varChar(300),
ConceptID int identity primary key
)
Create Table ConceptMapping (
ConceptID not null foreign key references Concepts,
ObjectID foreign key references Objects,
AttributeID foreign key references Attributes,
XmlID foreign key references XmlClobs
)
You can actually get away with just two tables: Objects, and Attributes, but a third table to map these together lets you reuse the same attribute between several objects.
The concepts table isn't necessary, but gives you the ability to provide something like folders in your application. An "object" can be anything - an email thread, a powerpoint slide, a goal for your web site, the content for an article ... so putting these broad categories in a concepts table lets you query specifically for what you want without having to wade for it.
An attribute can be a "name-value pair" like a registry key or a definition list in html. That's more efficient. But, if an object has 200 attributes you want to track, you can store all of this in Xml instead ... XmlClob is a really bad name, but XmlData was taken. You can change this to suit our purposes.
Hope this helps.
