Re: Database Design
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 25 Jul 2007 21:32:54 +0000 (UTC)
Paul (paulwragg2323@xxxxxxxxxxx) writes:
I have posted a question in the Database design and theory ng, but Ihttp://groups.google.co.uk/group/comp.databases.theory/browse_frm/thread/5db
expect a lot of you will have suggestions to help me (and that ng
doesn't seem very active).
The post is here:
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@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
.
- Follow-Ups:
- Re: Database Design
- From: Paul
- Re: Database Design
- References:
- Database Design
- From: Paul
- Database Design
- Prev by Date: Re: Pass Table as a parameter to a function
- Next by Date: Re: Establishing Precedence In ORDERBY Condition Causing Problems.
- Previous by thread: Database Design
- Next by thread: Re: Database Design
- Index(es):
Relevant Pages
|
Loading
