|XP is just a number|
Well Designed RDBMSby nothingmuch (Priest)
|on Apr 07, 2003 at 14:01 UTC||Need Help??|
nothingmuch has asked for the wisdom of the Perl Monks concerning the following question:
I want to reprisent a chunk of data (probably a BLOB), and a set of dynamic attributes (which might not be the same for each record, perhaps with many different attribute possibilities). I was wondering which of the following methods is preferred for such a system:
Attributes in another table - a new table with the following structure is made:
The advatage of doing this is mainly that you can look up the attributes more easily, remove and add them in a simple manner, and it's more with the spirit of databases. The downside is that it will probably result in many more reads / seeks / whatever, to load the attributes, than
Attributes as a blob - store some kind of serialized (packed or Storable'd) data structure in another blob, on the same row as the data, which contains the attribute name / data pairs all mushed together. This means that we can't (easily) search by attributes, but loading of the attributes will probably be much quicker. If the attribute set grows large there is more redundance in reading / writing of data, but that probably won't happen.
Which would you guys probably use? Should I consider this based on the database server aswell, or will it not make that much of a difference? Should i not even consider such backwards thinking and simply create a column for each possible attribute? Are there &lsb;dis&rsb;advantages i haven't considered and should? Is it too redundant to hybrid the two? Or is that even more backwards?
Thanks in advance,
zz zZ Z Z #!perl