|Perl Monk, Perl Meditation|
Re: OO concepts and relational databasesby Solo (Deacon)
|on Aug 02, 2004 at 15:13 UTC||Need Help??|
A table cross-referencing each row from the class table with rows from the attributes table. This kind of table would contain three columns:
I've used this approach myself, but it comes with some drawbacks.
By coercing all values into a single column, you lose datatype information. If the optional data (or sparse data, let's say, since optional data that is dense is probably better stored directly in the table anyway) is all of one type this isn't really a problem. But if numerics and dates are sprinkled in, the design gives up the ability to easily construct SQL statements involving conditions on those fields. Views can help with this to an extent.
This structure is not convenient for storing both attributes that may have one value and attributes that may have many. This choice represents either a unique key pair of object and attribute or no key. An additional column for attribute 'order' would allow a unique key in these circumstances, but is unnecessary for single-value attributes. A bit of a data-integrity headache. I used two tables (one for sparse single-valued- and one for sparse multiple-valued-attributes).
I don't have a ton of experience across opensource RDBMS, but what little I have tells me join speeds vary greatly. YMMV.
...adding new attributes can be done on the fly. Storing the optional attributes as columns requires a database change for every new attribute.
Indeed, this is really the only way I've seen 'End-User' specified attributes handled in RDBMS without allowing schema changes. If anyone has seen different implementations, I'd be interested in hearing about or seeing them.