table structure problem

[Tom Leylan]

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

[David Portas]

It's very difficult to give reliable design advice online without the
opportunity to analyse a particular business scenario in detail. With that
caveat in mind and based on the information given I would suggest a design
as follows. Customers and Lots are clearly different entities for most
purposes so keep them separate and use constraints to validate the data:

CREATE TABLE Vehicles (vin VARCHAR(10) PRIMARY KEY, lotid INTEGER NULL
REFERENCES Lots(lotid), customerid INTEGER NULL REFERENCES Customers
(customerid), CHECK ((lotid IS NOT NULL AND customerid IS NULL) OR (lotid IS
NULL AND customerid IS NOT NULL) /* Lot or Customer but not both */ ))

If you want to retrieve the location as a single column, you can use
COALESCE:

SELECT COALESCE(lotid, customerid) AS location
FROM Vehicles

You can also ensure that the same id is not used as both Lot and Customer:

CREATE TABLE VehicleLocation s (locationid INTEGER PRIMARY KEY, loctype
CHAR(1) CHECK (loctype IN ('L','C')), UNIQUE (locationid, loctype))

CREATE TABLE Customers (customerid INTEGER PRIMARY KEY, loctype CHAR(1) NOT
NULL CHECK (loctype = 'C'), FOREIGN KEY (customerid,loc type) REFERENCES
VehicleLocation s (locationid,loc type))

CREATE TABLE Lots (lotid INTEGER PRIMARY KEY, loctype CHAR(1) NOT NULL CHECK
(loctype = 'L'), FOREIGN KEY (lotid,loctype) REFERENCES VehicleLocation s
(locationid,loc type))

--
David Portas
------------
Please reply only to the newsgroup
--

[Tom Leylan]

"David Portas" <RE************ *************** *@acm.org> wrote...

It's very difficult to give reliable design advice online without the
opportunity to analyse a particular business scenario in detail.
No doubt, I had hoped I had conveyed the gist of it. So rather than discuss
"code" you're basically suggesting that the vehicle row would contain two
id's, one each for the two "types" of places it might be found? It is one
of the scenarios I considered. What I don't like about it (though I may be
stuck) is that it isn't impossible to predict a time when there would be a
third type of place the vehicle could be. It could be at a repair center
for instance. There would be a table of them (they are different than lots
and customers) and each would have an Id of course. At that point I have to
add a third Id field to the vehicles table.

I am in no way denigrating your suggestion or "throwing something in at the
last moment" I am only considering that all apps grow in time and personally
I like to have some room for growth. That said, I can't think of a better
way at the moment.

And it might work well. I can retrieve all the vehicles of course. The
ones that are at a lot would have a non-null LotId, the ones at a customer
have a non-null CustomerId. All vehicles at a particular lot or with a
particular customer can be isolated.
You can also ensure that the same id is not used as both Lot and Customer:

That part is handled, I have an ID "service" which assigns all the Ids.

Thanks,
Tom

[David Portas]

Maybe:

CREATE TABLE Vehicles (vin VARCHAR(10) PRIMARY KEY, locationid INTEGER NOT
NULL REFERENCES VehicleLocation s (locationid))

Then you can easily add new location entities such as Repair Centres in the
same way as for Customers and Lots. Although if you don't need to capture
much information about the locations you could probably still manage with
two tables: one for Customers and one for other locations.

--
David Portas
------------
Please reply only to the newsgroup
--

[Tom Leylan]

"David Portas" <RE************ *************** *@acm.org> wrote...

CREATE TABLE Vehicles (vin VARCHAR(10) PRIMARY KEY, locationid INTEGER NOT
NULL REFERENCES VehicleLocation s (locationid))

Then you can easily add new location entities such as Repair Centres in the same way as for Customers and Lots. Although if you don't need to capture
much information about the locations you could probably still manage with
two tables: one for Customers and one for other locations.

Hi David,

I'm not actually using MS-SqlServer (at least that can't be a guarantee) so
standard SQL queries have to produce the results. Doing it this way I can
see that I can find out if the vehicle is at a lot or out to a customer
through the code in VehicleLocation s table but I don't think a single query
will retrieve either the lot or customer information without my knowing
which table to look in, do I have that right?

Now that I look at it I'm not sure I can do that in a single query using the
multi-id column solution either. I can't choose the lot or customer table
until I know if the vehicle is rented. If I can, do you have a moment to
reply with the queries that would retrieve two things. One whether a
vehicle is rented or not (I think it can do that) and the other, the name of
the customer if it is rented (but you don't know if it is) where vin =
"12345"

Tom

[David Portas]

You can do the query you want under either table design:

SELECT V.vin, V.locationid,
COALESCE(L.lot_ desc, C.cust_name),
CASE WHEN C.customerid IS NOT NULL
THEN 'RENTED' ELSE 'NOT RENTED' END
FROM Vehicles AS V
LEFT JOIN Lots AS L
ON V.locationid=L. lotid
LEFT JOIN Customers AS C
ON V.locationid = C.customerid
WHERE vin='12345'

SELECT V.vin, COALESCE(V.loti d,V.customerid) ,
COALESCE(L.lot_ desc, C.cust_name),
CASE WHEN C.customerid IS NOT NULL
THEN 'RENTED' ELSE 'NOT RENTED' END
FROM Vehicles AS V
LEFT JOIN Lots AS L
ON V.lotid=L.lotid
LEFT JOIN Customers AS C
ON V.customerid = C.customerid
WHERE vin='12345'

--
David Portas
------------
Please reply only to the newsgroup
--

[Tom Leylan]

"David Portas" <RE************ *************** *@acm.org> wrote...

You can do the query you want under either table design:

Thanks David... I'm going to have a problem implementing that though (not
your problem of course) since the implementation of SQL I'm using doesn't
have COALESCE or CASE.

I might implement the structure and use two queries.

Thanks again,
Tom

[Mike Sherrill]

On Wed, 17 Sep 2003 21:10:18 GMT, "Tom Leylan"
<ge*@iamtiredof spam.com> wrote:

[snippage interspersed]

So the question is how do I layout the tables (and query) to find out where
a specific car is?
In general, you can't know where a vehicle is when it's rented. Think
about a library and its books. Similar issues.
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.
A customer is not a lot.
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.

I think you're struggling painfully toward an abstraction that
recognizes that there are many things you can do with a rental car.
You can rent it, lease it, sell it, send it to a garage for service,
loan it to your brother-in-law, have it stolen, misplace it, and so
on. Rentals require one set of attributes, sales require another set,
service requires yet another, and so on.

So you're looking at a table of vehicle dispositions (kind of like a
supertype), a supporting table for each dispostion (kind of like a
subtype), a view for each disposition (to make life easier by joining
the "kind of" supertype with its "kind of" subtype), and several
constraints to keep the data clean.

Did that make sense?

--
Mike Sherrill
Information Management Systems

[Tom Leylan]

"Mike Sherrill" <MS*******@comp userve.com> wrote...

In general, you can't know where a vehicle is when it's rented. Think
about a library and its books. Similar issues.
Hi Mike... I don't follow this, when did libraries stop tracking which books
you've borrowed? You can't think I mean "where" in terms of latitude and
longitude and in the case of a library book they don't care which shelve (if
any) you have it on. That you have it is it's "location" right?

Clearly there can be a
column in the car table containing a LocationId but that wouldn't indicatewhether it is a LotId or a CustomerId.

A customer is not a lot.

That seems obvious. I'm still don't get your point.
You can rent it, lease it, sell it, send it to a garage for service,
loan it to your brother-in-law, have it stolen, misplace it, and so
on. Rentals require one set of attributes, sales require another set,
service requires yet another, and so on.
Loaning it to a brother-in-law doesn't need to be tracked. Similarly
(should you ask) inching it forward on Main Street or backing out of the
driveway doesn't need to be tracked either... and I still don't get your
point. :-)
So you're looking at a table of vehicle dispositions (kind of like a
supertype), a supporting table for each dispostion (kind of like a
subtype), a view for each disposition (to make life easier by joining
the "kind of" supertype with its "kind of" subtype), and several
constraints to keep the data clean.

Did that make sense?

Nope. I can create tables designed to hold information about your
brother-in-law, the garage, the rental and the sale okay? Now how do I know
which table to check or is your solution to check them all noting which
record has the most current date/time of occurence? That's a lot of work to
find out if the car is in a lot available for rent.

I think I can do all this with with a single LocationId, LocationType pair
kept in the vehicle table. I'm working on that strategy at the moment.

[rkc]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:1a******** ********@twiste r.nyc.rr.com...

"Mike Sherrill" <MS*******@comp userve.com> wrote...

So you're looking at a table of vehicle dispositions (kind of like a
supertype), a supporting table for each dispostion (kind of like a
subtype), a view for each disposition (to make life easier by joining
the "kind of" supertype with its "kind of" subtype), and several
constraints to keep the data clean.

Did that make sense?

Nope. I can create tables designed to hold information about your
brother-in-law, the garage, the rental and the sale okay? Now how do I

know which table to check or is your solution to check them all noting which
record has the most current date/time of occurence? That's a lot of work to find out if the car is in a lot available for rent.

I think I can do all this with with a single LocationId, LocationType pair kept in the vehicle table. I'm working on that strategy at the moment.

I think you are on the right track, with one minor suggestion. While
location,
(as indicated by a LocationID) is more or less an attribute of a vehicle,
location type is not. All you need in the Vehicle table is a LocationID.
Type can be determined by a join with the Location table. That makes
LocationType (an attribute of Location) in the Vehicle table redundant.