Beefy Boxes and Bandwidth Generously Provided by pair Networks Joe
"be consistent"
 
PerlMonks  

Re: OO concepts and relational databases

by Solo (Deacon)
on Aug 02, 2004 at 15:13 UTC ( #379306=note: print w/ replies, xml ) Need Help??


in reply to OO concepts and relational databases

A table cross-referencing each row from the class table with rows from the attributes table. This kind of table would contain three columns:
EntityAttributeValue

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.

--Solo
--
You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.


Comment on Re: OO concepts and relational databases

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://379306]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (9)
As of 2014-04-17 06:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (440 votes), past polls