[Dbix-class] flexible attributes on db objects

[Jess Robinson]

On Fri, 8 Jun 2007, Daniel McBrearty wrote:

> as my app is developing I'm seeing more and more that having
> flexibility in the db layer is one of the things that saves a lot of
> stress and hassle.
>
> something that happens a lot is that you get the basic objects and
> relationships between them correct, but don't know all the attributes
> that they will need until later. so (thanks Alan Humphrey for this)
> there is a way (that is likely not new to many people here) ... :
>
> TABLE my_object {
> id INTEGER PRIMARY KEY,
> .
> . // whatever
> .
> };
>
> TABLE my_object_attributes {
> id INTEGER PRIMARY KEY,
> name TEXT
> }
>
> TABLE my_object_attributes {
> my_object_id REFERENCES my_object(id),
> attribute_id REFERENCES my_object_attributes(id)
> text_value TEXT,
> number_value NUMBER
> }
>
> so now you have a way to add a new attribute to the object, and have
> any instance then have a text/number value for that.
>
> You can also write some API functions in the db layer to
> create/assign/delete attributes, taking table name as an arg. So at
> the cost of two tables for the object, you move a lot of design
> decisions downstream.
>
> Comments? horrendous hackery? laziness? a landmine? ok in some cases?
>

Been there, done that, bought the T-Shirt.

Pros: Flexibility, no need to design fully up-front, less work..

Cons: No constraints, no types or extra cpde to check types/number/text 
fields, which normally the db would do for you, no default values per 
attribute "type", no nullable check.. etc..

If you take this all the way, you're designing a db within the db, without 
all the benefits of its built-in checking/optimising.

That's not to say it can't be useful, it can, but you should make sure you 
know/define the "rules" for the type/subset of data you are storing this 
way, and don't overstep them.

Jess