Re: Help with Design and Obtaining of Data

On Wed, 25 Jul 2007 07:38:56 -0700, paulwragg2323@xxxxxxxxxxx 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
.

Relevant Pages

  • Help with Design and Obtaining of Data
    ... similar representation of how our database was when we inheritied it. ... one straighforward query. ... So the data in the Phones table may be: ... PH.VALUE FROM PHONES WHERE PERSONID = PER.ID AND TYPE ...
    (comp.databases.theory)
  • Re: Controlling Data Formatting
    ... But the fact that you've hit this snag means that your database ... PersonID ... a record in tblPersonsTrainingItems like this: ... >does not require certain training items. ...
    (microsoft.public.access.gettingstarted)
  • Re: Command buttons - Access 2003
    ... line of code to the form's On Got Focus event: ... The database has been used so ... A contact phones to tell me they have changed ... I created a command button on the ...
    (microsoft.public.access.forms)
  • Re: Editing Union results
    ... UNION queries are never updateable and cannot be made ... 8i+, SQL Server 7.0+, etc., by using "INSTEAD OF" triggers on views made ... the data from the form back into another function back into the database. ... personID ...
    (microsoft.public.access.queries)
  • Re: A Question of design.
    ... Should I really query the database that many times? ... a single query, and if so, how would I know what items the user already ... then you know you have a duplicate. ... ON DUPLICATE KEY UPDATE syntax I ...
    (comp.lang.php)