Hi,

I have found a problem that I can't seem to solve in what I consider a
nice way.

Basicly I have a table called Jobs. Each Job has some details in its
table, it also has a type. These are A, B, C, D, E and F. If it is
of type A then it needs an additional 10 columns, B 13, C 30 and so
on. These additional piecies of information are totally unrelated to
those in a different letter. How do I represent this?

My first thought was to have a Jobs table which has a foreign key
pointing to a Type table, which lists the types, and then a column
which has a foreign key to either Table A, Table B, Table C etc. This
would them mean that there was a foreign key that pointed to different
tables depending on what another column's data said.

Is that even slightly valid relational database thinking? If not what
is a better solution? All I could really think of was having an A_key
column, a B_key column etc, but then there would be loads of null
data.

Cheers,

Chris

Stop designing a physical model and design your logical model first. Once
you have the logical model, converting to a physical model is easy.

"Chris" <chris (AT) cjetech (DOT) co.uk> wrote

In message <220db75.0309170535.21a6a49b (AT) posting (DOT) google.com>, Chris
<chris (AT) cjetech (DOT) co.uk> writes
That's perfectly valid. It only seems strange if you expect to use an
identity column in the Jobs table. The foreign keys in each of the
linked tables would not form a contiguous sequence.

This table layout represents a supertype with data for each subtype
stored in a different table. It's a valid design that is compatible with
the logical data model as you have described it.

Nulls are OK. You could create a single table with lots of fields which
are ignored when the type is not A. This merges the supertype and
subtype into one table. It is also a completely valid structure and
compatible with the logical data model as you have described it.

Which you choose is likely to depend on implementation details. The
single table design is easier to build but may eat unacceptable amounts
of storage space at run-time. If there are only a few records in the
table the storage overheads will be negligible. One or the other version
may run faster on your particular RDBMS. Is run-time speed a big issue?



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Chris wrote:
That's not really a foreign key because these always point to the same
table. The usual solution is to simply reverse them and give the tables
Table_A, Table_B, et cetera, a foreign key (which is identical to their
primary key) and that points to the primary key of the Jobs table. This may
look like the wrong way around but you get real foreign keys that way which
are easier for the DBMS to maintain.

-- Jan Hidders

Sorry to barge in on your thread but I think I have a similar problem and it
is possible that a similar solution would work for both. I posted this
message in comp.databases.ms-sqlserver but I'd like to consider all ideas
particularly since I'm not using MS-SqlServer. Your "jobs" is my "cars" and
instead of many types I have two (at the moment) a "lot" and a "customer."
I can't seem to determine where a car is in one query. I have to know if it
is rented or not, similarly you seem to need to check the type first and
this isn't so good.

Tom



<begin message>

Help :-) I can't see how this should be laid out!

Consider the scenario of a car rental company. They have "cars" and "lots"
(nationwide) and "customers." So far it is easy to query each of these. A
car is rented by a customer so there would be say a "rentals" table. A car
is rented and returned over and over so there would be many rentals for each
car. One thing to remember is that the car isn't assigned to a specific lot
so either the customer has the car or it is in a lot (somewhere) and
available for rental.

So the question is how do I layout the tables (and query) to find out where
a specific car is? Or where all the cars are for that matter. I don't
think it is as simple as it looks at first glance. Clearly there can be a
column in the car table containing a LocationId but that wouldn't indicate
whether it is a LotId or a CustomerId. There could be a column for both
LotId and CustomerId and with only one filled in at a time but that doesn't
lend itself to an elegant SQL query does it?

I thought there might be a LocationIdType field. It could be set to
indicate whether the LocationId is a "lot" or a "customer" which permits me
to join with the lots or customers table but I wondered if there is another
way.

Is that enough information to go on? I can give more details if it helps.

Thanks,
Tom

joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message news:<a264e7ea.0309201154.69d8aa12 (AT) posting (DOT) google.com>...
[...]

Shouldnt that be

CREATE TABLE A_jobs
(job_ticket INTEGER NOT NULL PRIMARY KEY,
....

Since there is a foreign key (job_ticket, job_type), is it necessary
to have the foreign key (job_ticket)?

[...]


Kind regards
/Lennart

You are modeling a hierarchy among types. The Job is a super abstract type.
What you want to put in TableA/B etc are concrete subtypes. What you put in
the Type table are names of the subtypes.

RDBMS doesn't give you a nice way of modeling this object-oriented
structure. The dirty, quick and tacky way is to slamm all of the subtypes
into one table. The results is that many columns will have predictabe null
values depending on the subtype. OO developers will need to create a Job
abstract class and TypeAJob etc class for each subtypes and use the relevant
column as the class's properties. This is highly labor-intensive. If you
decide to add a subtype or add a column, they will go back to the drawing
board. Separating the subtypes into different table may make the model more
comprehensible, but doesn't really help. You 'll have proliferation of
tables, columns, foreign keys and sql statements.

If you are not happy with the aproaches, and are open-minded for something
more elegant, take a look at http://www.geocities.com/unifiedmodel
which separates the hierarchy of types and the relationships among objects.

K

"Chris" <chris (AT) cjetech (DOT) co.uk> wrote

Hi,

I have found a problem that I can't seem to solve in what I consider a
nice way.

Basicly I have a table called Jobs. Each Job has some details in its
table, it also has a type. These are A, B, C, D, E and F. If it is
of type A then it needs an additional 10 columns, B 13, C 30 and so
on. These additional piecies of information are totally unrelated to
those in a different letter. How do I represent this?

My first thought was to have a Jobs table which has a foreign key
pointing to a Type table, which lists the types, and then a column
which has a foreign key to either Table A, Table B, Table C etc. This
would them mean that there was a foreign key that pointed to different
tables depending on what another column's data said.

Is that even slightly valid relational database thinking? If not what
is a better solution? All I could really think of was having an A_key
column, a B_key column etc, but then there would be loads of null
data.

Cheers,

Chris

Yep, we need a priamry key in each of the job tables to prevent
duplicates.

CREATE TABLE A_jobs
(job_ticket INTEGER NOT NULL PRIMARY KEY
REFERENCES Tickets(job_ticket),
job_type CHAR(1) DEFAULT 'A' NOT NULL
CHECK(job_type = 'A'),
FOREIGN KEY(job_ticket, job_type)
REFERENCES Tickets (job_ticket, job_type)
ON DELETE CASCADE
ON UPDATE CASCADE,
...);

But I do need the FOREIGN KEY (job_ticket, job_type) to make that the
job_ticket bellongs to one and only one job_type.

joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message news:<a264e7ea.0309221130.64fc919d (AT) posting (DOT) google.com>...
I agree on that, but I was wondering whether

(job_ticket INTEGER NOT NULL PRIMARY KEY
REFERENCES Tickets(job_ticket),

is necessary. Couldnt we just do

(job_ticket INTEGER NOT NULL PRIMARY KEY,

Since job_type is fixed by the constraint

CHECK(job_type = 'A'),

the constraint

REFERENCES Tickets (job_ticket, job_type)

should be sufficient. I agree that there probably will not be any
overhead by including REFERENCES Tickets(job_ticket), but is it
strictly necessary, from a theoretical and/or practical point of view.


Kind regards
/Lennart