|
|
Posted by Erland Sommarskog on 07/25/07 21:32
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?hl=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@sommarskog.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
Navigation:
[Reply to this message]
|