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.
