Re: Help with Design and Obtaining of Data

From: Evan Keel <evankeel_at_sbcglobal.net>
Date: Wed, 25 Jul 2007 15:38:53 -0400
Message-ID: <VFNpi.4522$Dx2.3344_at_newssvr17.news.prodigy.net>


<paulwragg2323_at_hotmail.com> wrote in message news:1185374336.402678.195530_at_w3g2000hsg.googlegroups.com...
> Hi,
>
> We are currently in the process of attempting to come up with a
> solution to redesign our database. I am not going to use real
> examples, but I will attempt to give enough information to enable
> somebody to help me.
>
> At present we have 1 very large table that stores attributes of
> certain entities. For now I will use phone numbers as an example. If
> we have these columns in a table:
>
> Person
> --------------------------
> ID
> Name
> DOB
> Address
> HomeTel
> WorkTel
> MobileTel
> ExtraTel 1
> ExtraTel 2
> ...
>
> and then another 50 columns for other telephone numbers, this is a
> similar representation of how our database was when we inheritied it.
> We can easily obtain all of the data as:
> SELECT ID, NAME, DOB, ADDRESS, HOMETEL, WORKTEL..... in one go using
> one straighforward query.
>
> As a lot of the time these additional columns will not have data in,
> we want to make it so that they are records (which are only created
> when required) instead. So something along these lines for our tables:
>
> Person
> --------------------------
> ID
> Name
> DOB
> Address
>
> and then another table
>
> Phones
> --------------------------
> PersonID
> Type
> Value
>
> So the data in the Phones table may be:
>
> PersonID Type Value
> --------------------------------------------------------------------------



> 1 Home 012...
> 1 Mob 013...
> 1 Work etc
> 2 Home
> 3 Mob
>
> I believe that this is the best way to store the data. The problem I
> have is getting the data out in a single query, without using syntax
> such as
>
> SELECT PER.ID, PER.DOB
> (SELECT PH.TYPE, PH.VALUE FROM PHONES WHERE PERSONID = PER.ID AND TYPE
> = 'Home'),
> (SELECT PH.TYPE, PH.VALUE FROM PHONES WHERE PERSONID = PER.ID AND TYPE
> = 'Mob'),
> ....
> etc
>
> I need the data in the most efficient way possible, in a similar
> manner to as it was previously.
> In our real database, we have over 450 of these attributes, and it has
> now got to the point where it is a big performance hit - the table has
> nearly 500 columns.
> If anybody can suggest anything that would help along these lines, a
> solution, or pointers on how to handle this it would be greatly
> appreciated.
>
> Thanks in advance!
>
> Paul
>

I would go with the phone numbers in their own table, much easier to add new phone number types. Also, queries are not complex, just a join on on PersonID. Received on Wed Jul 25 2007 - 21:38:53 CEST

Original text of this message