Re: scaleable design for multiple value tuple

--On Friday, October 17, 2003 3:45 -0700 dev o'null
<devicenull(at)linuxmail(dot)org> wrote:

> 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

Ok, sounds good so far. Here's what I think I have as your table
structure:

Table1:
id
case

Table2:
case
engineer

There could well be a table three, with info about the engineers too,
but it is probably irrelevant. Not a bad structure.

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

So then the two tables above would be combined into:

Table1:
case
id
engineers

Where engineers is a combined listing of all the engineers on the
case.

This is fairly good, if you aren't storing info on the engineers
elsewhere. If you are, the logic to keep the engineers above synced
to those engineers gets complicated. Can be done though.

You can use an array column to do this as well, just specify the
table like so:

CREATE TABLE cases (
case int,
id serial,
engineer text[] );

Which lets you have more than one engineer. Still, I would prefer
another table: it is easier to use and search.

> 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?

How you want to structure the data is really up to you; you are the
only one who knows all the requirements. However, I'd use separate
tables (linked with forgen keys), just because it is the most
flexible.

As for 'the view would then contain three lines'... So, make a
better view. Very few people actually look at the database directly
after all ;-).

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

Which repetition? In none of the above cases is there repetition,
really. Each row in each table is a unique piece of information.

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

Shouldn't be too hard, in most cases ;-). I'd first try doing it on
the client side, but then I'm more of a perl programmer than a SQL
programmer. There is probably a good way to do this in SQL
(subquerys maybe? Or would you have to use the string concatenation
operator? Hmmm...), but I'd have to test it and I don't have the
gumption to come up with good test data at the moment. Keep
prodding, and you'll probably get someone to come up with something.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------