Re: Database Design

Paul (paulwragg2323@xxxxxxxxxxx) writes:
The design you aim at is known as EAV, and many frown at it.

Is there a 'better' method for storing this type of data? If so I
would be interested to know about it.

Without knowledge about what your tables really contains, I don't want to
say much more of what is good or bad. I just wanted to mention that
the deisgn you are looking has a bad name.

SELECT per.id, per.dob,
Home = MIN(CASE ph.Type WHEN 'Home' THEN ph.Value END),
Mob = MIN(CASE ph.Type WHEN 'Mob' THEN ph.Value END),

This works very well actually, and the execution plan seems very
reasonable compared to previous efforts! There is a problem though, in
that some of the attributes are defined as text columns, which of
course we cannot use the MIN aggregate function on. Do you have any
suggestions for how we can include text columns using similar syntax
and without using sub-selects?

Still on SQL 2000? In SQL 2005, you could use varchar(MAX) which
should work with MIN.

One alternative is to do a lot of joins:

SELECT per.id, per.dbo, Home = home.Value, Mob = Mob.value
FROM persons per
LEFT JOIN phones Home ON per.person = Home.person
AND Home.type = 'Home'
LEFT JOIN phones Mob ON per.person = Mob.person
AND Mob.type = 'Mob'


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.