On Feb 5, 2008, at 10:17 AM, Alex Turner wrote:
On Feb 4, 2008 7:09 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
On Feb 3, 2008 10:14 PM, Alex Turner <armtuk@xxxxxxxxx> 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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
