scaleable design for multiple value tuple records

From: devicenull(at)linuxmail(dot)org (dev o'null)
To: pgsql-novice(at)postgresql(dot)org
Subject: scaleable design for multiple value tuple records
Date: 2003-10-17 10:45:29
Message-ID: 5e99f0fe.0310170245.3c79812@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

not even sure what is the correct database term to describe this
problem.

while maintaining the unique id of a case record [row], i have some
tuples which i want to be capable of forming another dimension of
records [rows]. not even sure if this is how it is done.

example.
id case engineer
1 20031017 sd, pk, ln

so i have three engineers working on one case number.
i want to maintain one table just for the case numbers and their ids.

id case

then i want to have a table just for case numbers and engineers.

case engineer
20031017 sd
20031017 pk
20031017 ln

in the case of multiple engineers working on one case, is it usual
that a relational database design would create another dimension for
the tuple 'engineer' and then begin a new series of records, one
record per engineer value?

what i am visualizing is this:


case---engineer tuple-----other field
|
|
|
----------
| | |
sd pk ln

and so maintaining one actual record for this case if it were one
table.

or should i leave separate tables?
in the case of separate tables the view would then contain three lines
for this one case. which i wanted to avoid. is there a better way?

what i want to achieve is efficiency in the database by eliminating
the repitition of records for each engineer working on the same case.

i have considered maintaining a table with a key on case numbers and
just one tuple for the engineer. this reduces the repitition to only
case numbers which is affordable. however i wonder how this is
reportable in a database format.

in the report output i do not want to see three records for the above
case (one for each engineer). i just want to see a view of the case
but including which engineers belong to that case.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Dave 2003-10-17 13:53:28 Trouble creating a survey database
Previous Message David Rickard 2003-10-16 18:00:47 Date Formats and Conversions