Database Design

[Paul]

Hi all,

I have posted a question in the Database design and theory ng, but I
expect a lot of you will have suggestions to help me (and that ng
doesn't seem very active).

The post is here:

http://groups.google.co.uk/group/comp.databases.theory/browse_frm/thread/5db717bb37ca5b0f?hl=en

Any help would be appreciated.

Paul

[Erland Sommarskog]

Re: Database Design

Paul (paulwragg2323@ hotmail.com) writes:

I have posted a question in the Database design and theory ng, but I
expect a lot of you will have suggestions to help me (and that ng
doesn't seem very active).
>
The post is here:
>
>

http://groups.google.co.uk/group/comp.databases.theory/browse_frm/thread/5db

717bb37ca5b0f?h l=en

>
Any help would be appreciated.

Your intended design may or may not make sense. I recall that exactly in
the case of telephone numbers we did that transformation in our system.

The design you aim at is known as EAV, and many frown at it. But there are
definitely cases where this design make sense.

What is the problem with it? You've already discovered it: writing the
queries. They are more difficult to write, and if you misspell and
attribute code, there is no compiler to tell you.

For the particular query you had problem with, this is the best way of
writing it:

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),

But if you find that you need to write a lot of those queries, you are
probably going in the wrong direction.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

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

[Paul]

Re: Database Design

Hi Erland,

>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.

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?

Thanks again,

Paul

[Erland Sommarskog]

Re: Database Design

Paul (paulwragg2323@ hotmail.com) 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@sommarsk og.se

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