Help with Design and Obtaining of Data
Date: Wed, 25 Jul 2007 07:38:56 -0700
Message-ID: <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 Received on Wed Jul 25 2007 - 16:38:56 CEST