Help with Design and Obtaining of Data
Date: Wed, 25 Jul 2007 07:38:56 -0700
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:
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:
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:
and then another table
So the data in the Phones table may be:
PersonID Type Value
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'),
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