Adam W wrote:
Hi,
Can anyone put a name to the following type of design...
I dunno what you'd call it, except perhaps a violation of Boyce-Codd
Normal Form. I've seen a Lotus Domino DB2 database that did this, but
I'm not sure if that's the way it usually works or if this was a custom
thing.
I need to be able to modify the amount/names of data "fields" stored
for an entity so instead of representing the data stored for an entity
in fields associated with that entity you would relate it to an
another entity which would contain a datafield type field and its
associated value, so different rows in the parent table could possibly
have different datafields associated with it.
E.g
The parent table is "person" and it holds common data amongst all
people - it has primary key of personID.
There is another table assoicated with person called "personDetails".
This would have fields "personID", "dataType" and "dataValue". It
might have records like the following:
personID, dataType, dataValue
1,phone,123456
1,email,test@test.com
1,state,NSW
2,phone,987456321
2,state,VIC
3,phone,789456123
Of course this is just an example - the parent entity could be anything.
Hope someone can put a name to this sort of design. I want to research
into this to see how people search effectively in this design and its
performance compared to traditional methods etc etc.
Performance was ridiculously bad, and getting the data back out into a
more sane format was a nightmare.
Searching will suck because the index over the dataValue column will be
trying to do several things at once, and missing values will be a problem.
I have done it myself once, but I knew it was a nasty hack, and didn't
care about the performance issues. It was a nice easy way to get the
ACID stuff for free without trying to re-invent the wheel.
I'd say that if you don't want your data to be relational, there's not
much reason to be using a RDBMS. Maybe an object database would be a
better idea if that's what you really want to do.
Any help would be appreciated.
I wouldn't do it. =)
--
Felix
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
