Re: Help with Design and Obtaining of Data

On Wed, 25 Jul 2007 07:38:56 -0700, paulwragg2323_at_hotmail.com wrote:

>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.
>
> (...)
>
>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
>

As I see it, your problem is not getting the data in one query but getting the data in the same way as before, i.e. in one record, so that your application(s) see the same thing. I think ideally you would also refactor your app(s) to work with a single join where you then retrieve the different "attributes" row by row.
If that is not (yet) possible I'd just hide the complex statement behind a view and check if the select performance doesn't take a severe hit. (Maybe your database supports different methods like pipelined functions and whatnot ...)

br,
Martin Received on Thu Jul 26 2007 - 08:40:31 CEST