Re: Foreign key pointing to multiple tables

> Chris <chris_at_cjetech.co.uk> writes

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

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 Received on Thu Sep 18 2003 - 04:16:49 CEST