Re: [OT] "advanced" database design (long)

On Feb 5, 2008, at 10:17 AM, Alex Turner wrote:

> On Feb 4, 2008 7:09 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Feb 3, 2008 10:14 PM, Alex Turner <armtuk(at)gmail(dot)com> wrote:
>>> I"m not a database expert, but wouldn't
>>>
>>> create table attribute (
>>> attribute_id int
>>> attribute text
>>> )
>>>
>>> create table value (
>>> value_id int
>>> value text
>>> )
>>>
>>> create table attribute_value (
>>> entity_id int
>>> attribute_id int
>>> value_id int
>>> )
>>>
>>> give you a lot less pages to load than building a table with say
>>> 90 columns
>>> in it that are all null, which would result in better rather than
>>> worse
>>> performance?
>>
>> But you're giving us a choice between two bad methodologies.
>>
>> Properly normalized, you'd not have a table with 90 nullable columns,
>> but a set of related tables where you'd only need to store things in
>> the subordinate tables for the relative data points.
>>
>> The worst thing about EAV is that it makes it very hard to figure out
>> what the heck is going on by just looking at the database schema.
>> It's easy to develop and hard to maintain. We had a person do
>> something like that last place I worked and it took weeks for a new
>> developer to figure it out and replace it with a properly relational
>> model, because there were little corner cases all through the code
>> that kept popping up.
>>
> I just thought of another problem, the system can have multiple values
> for a single attribute. How do you normalise that without basically
> adding a link table that's just the same thing as given below (I know
> there are array types in Postgresql, but there aren't in other DBs and
> I'm a fan of keeping products as DB neutral as possible)?

No, it wouldn't. You're confusing mechanics with semantics. In that
case the data would have meaning and context inherent in the schema
relationship between the two tables that would have the one-to-many
relationship you describe. In addition, you'd be able to take
advantage of foreign keys and other kinds of constraints to enforce
data integrity. Those are the intended goals (at a high level) of
relational data theory in the first place.

Seriously, though, go pick up a book on relational database design,
most cover this very topic at some point.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com