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]

Re: table structure problem

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]

Re: table structure problem

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote...
[color=blue]
> It's very difficult to give reliable design advice online without the
> opportunity to analyse a particular business scenario in detail.[/color]

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.
[color=blue]
> You can also ensure that the same id is not used as both Lot and Customer:[/color]
That part is handled, I have an ID "service" which assigns all the Ids.

Thanks,
Tom

[David Portas]

Re: table structure problem

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]

Re: table structure problem

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote...
[color=blue]
> 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[/color]
the[color=blue]
> 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.[/color]

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]

Re: table structure problem

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]

Re: table structure problem

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote...
[color=blue]
> You can do the query you want under either table design:[/color]

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]

Re: table structure problem

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

[snippage interspersed]
[color=blue]
>So the question is how do I layout the tables (and query) to find out where
>a specific car is?[/color]

In general, you can't know where a vehicle is when it's rented. Think
about a library and its books. Similar issues.
[color=blue]
>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.[/color]

A customer is not a lot.
[color=blue]
>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.[/color]

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]

Re: table structure problem

"Mike Sherrill" <MSherrill@comp userve.com> wrote...
[color=blue]
> In general, you can't know where a vehicle is when it's rented. Think
> about a library and its books. Similar issues.[/color]

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?
[color=blue][color=green]
> >Clearly there can be a
> >column in the car table containing a LocationId but that wouldn't[/color][/color]
indicate[color=blue][color=green]
> >whether it is a LotId or a CustomerId.[/color]
>
> A customer is not a lot.[/color]

That seems obvious. I'm still don't get your point.
[color=blue]
> 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.[/color]

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. :-)
[color=blue]
> 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?[/color]

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]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:1aGab.7779 $u67.512@twiste r.nyc.rr.com...[color=blue]
> "Mike Sherrill" <MSherrill@comp userve.com> wrote...[/color]
[color=blue][color=green]
> > 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?[/color]
>
> 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[/color]
know[color=blue]
> 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[/color]
to[color=blue]
> 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[/color]
pair[color=blue]
> kept in the vehicle table. I'm working on that strategy at the moment.[/color]

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.

[Tom Leylan]

Re: table structure problem

"rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...[color=blue]
> "Tom Leylan" <gee@iamtiredof spam.com> wrote...[color=green]
> > 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.[/color][/color]
[color=blue]
> 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.[/color]

Hi... I appreciate the response.

While you are describing the classis solution it turns out this isn't the
classic "many-to-many" problem. Each vehicle is in at most one location the
problem is that we don't know which type of location it is and therefore we
don't know which table to use for the query.

There isn't actually a "location" but rather the location is either a "lot"
or a "customer." While it is possible to introduce a location table the Ids
kept there would be a mix of LotId's and CustomerIds. While there won't be
any common IDs in my system there certainly could be in other
implementations .

It is subtle but the LocationType turns out to be an attribute of the
vehicle just as the LocationId is. If the column in the vehicle table were
named CustomerId and the id there pointed to the customer you can see that
you know the Id "type" it is a customer id by virtue of it being stored in
that column. Another column could be LotId and similarly if I saw a value
it's type would be "lot." We know it's type.

So rather than having two columns, one of which is always null (and
importantly instead of adding another column each time another location type
was needed) I figured the LocationId would stand in for _any_ Id and a
LocationType field would identify which table that Id can be found in.

If it makes is easier to accept a LocationType, we can name it
VehicleStatus. Then it is set to "available" when the LocationId is a lot
and "rented" when the LocationId is a customer. It works the same either
way.

[rkc]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:kdkbb.1159 3$u67.4015@twis ter.nyc.rr.com. ..[color=blue]
> "rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...[color=green]
> > "Tom Leylan" <gee@iamtiredof spam.com> wrote...[color=darkred]
> > > 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.[/color][/color]
>[color=green]
> > 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[/color][/color]
is[color=blue][color=green]
> > 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.[/color]
>
> Hi... I appreciate the response.
>
> While you are describing the classis solution it turns out this isn't the
> classic "many-to-many" problem. Each vehicle is in at most one location[/color]
the[color=blue]
> problem is that we don't know which type of location it is and therefore[/color]
we[color=blue]
> don't know which table to use for the query.
>
> There isn't actually a "location" but rather the location is either a[/color]
"lot"[color=blue]
> or a "customer." While it is possible to introduce a location table the[/color]
Ids[color=blue]
> kept there would be a mix of LotId's and CustomerIds. While there won't[/color]
be[color=blue]
> any common IDs in my system there certainly could be in other
> implementations .
>
> It is subtle but the LocationType turns out to be an attribute of the
> vehicle just as the LocationId is. If the column in the vehicle table[/color]
were[color=blue]
> named CustomerId and the id there pointed to the customer you can see that
> you know the Id "type" it is a customer id by virtue of it being stored in
> that column. Another column could be LotId and similarly if I saw a value
> it's type would be "lot." We know it's type.
>
> So rather than having two columns, one of which is always null (and
> importantly instead of adding another column each time another location[/color]
type[color=blue]
> was needed) I figured the LocationId would stand in for _any_ Id and a
> LocationType field would identify which table that Id can be found in.
>
> If it makes is easier to accept a LocationType, we can name it
> VehicleStatus. Then it is set to "available" when the LocationId is a lot
> and "rented" when the LocationId is a customer. It works the same either
> way.[/color]

I'm sorry. I posted too soon. After thinking about the whole thing for a
while, particularly what Mike Sherrill had to say, I don't think that
LocationID
belongs in the Vehicle table either. My explanation would have to be quite
long winded so I'll only bother if you're interested.

[Tom Leylan]

Re: table structure problem

"rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...[color=blue]
> I'm sorry. I posted too soon. After thinking about the whole thing
> for a while, particularly what Mike Sherrill had to say, I don't think
> that LocationID belongs in the Vehicle table either. My explanation
> would have to be quite long winded so I'll only bother if you're
> interested.[/color]

Sure, I'm all ears. If you can, please keep in mind that I'm a little less
interested in a solution that only operates on the latest version of
MS-SqlServer (or any other particular product.) The problem I'm defining
has been around for decades and I have to assume it has been solved before
so a generic rather than proprietary solution would be best.

Tom

[rkc]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:Zpnbb.8043 $lZ6.2392184@tw ister.nyc.rr.co m...
[color=blue]
> Sure, I'm all ears. If you can, please keep in mind that I'm a little[/color]
less[color=blue]
> interested in a solution that only operates on the latest version of
> MS-SqlServer (or any other particular product.) The problem I'm defining
> has been around for decades and I have to assume it has been solved before
> so a generic rather than proprietary solution would be best.[/color]

Okey Dokey. Here goes.

Here's a first draft of an approach that makes the answer to the question
'Where are all the vehicles now?" possible.

Other questions that could be answered include

"What vehicles are available now?"
"Where's the yellow Hummer2?"
"How often do we service the Yugos?"
"What vehicles are currently out of service?"
"What is the most frequently rented vehicle type?"
"Where were the step vans on 9/11/2001?"
"Do you think we need more 4 wheel drive vehicles?"

[Tables]

Vehicles (VehicleID*, Type)
Type = Compact, Mini-Van, Full-Size, etc.

Locations (LocationID*, Type)
Type = Rental Lot, Service Station, Body Shop, In Transit, etc.

Rentals (VehicleID*, CustomerID*, DateRented*, DateReturned, TypeRequested)

LocationsVehicl es (LocationID*, VehicleID*, DateArrived*, DateDeparted)

Customers (CustomerID*, CustomerName)

[/Tables]

[Views]

CurrentVehicleL ocations
Question: Where are all the vehicles, disregarding rental status.
Also used in LocationOfUnRen tedVehicles view

SELECT
V.VehicleID,
V.Type,
L.LocationID,
L.LocationType,
LV.DateArrived
FROM Vehicles V INNER JOIN (Locations L
INNER JOIN LocationsVehicl es LV
ON L.LocationID = LV.LocationID)
ON V.VehicleID = LV.VehicleID
WHERE LV.DateDeparted Is Null;

OutStandingRent als
Question: What vehicles are currently out as rentals
and who rented them?
Also used in LocationOfUnRen tedVehicles view.
Also used in CurrentStatusOf Vehicles view.

SELECT
V.VehicleID,
V.Type,
"Rented" AS LocationID,
C.LastName & ", " & C.FirstName AS Customer,
RD.DateRented
FROM Vehicles V INNER JOIN (Customers C
INNER JOIN RentalDetails RD
ON C.EmployeeID = RD.CustomerID)
ON V.VehicleID = RD.VehicleID
WHERE RD.DateReturned Is Null;

LocationOfUnRen tedVehicles
Question: Where are the unrented vehicles?
Also use in CurrentStatusOf Vehicles view

SELECT CurrentVehicleL ocations.*
FROM CurrentVehicleL ocations
WHERE CurrentVehicleL ocations.Vehicl eID NOT IN
(SELECT VehicleID FROM OutStandingRent als);

CurrentStatusOf Vehicles

SELECT LocationsOfUnRe ntedVehicles.*
FROM LocationsOfUnRe ntedVehicles
UNION
SELECT OutStandingRent als.*
FROM OutStandingRent als;

[/Views]

[Tom Leylan]

Re: table structure problem

"rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...

Gosh you did a lot of work... thanks.
[color=blue]
> "What vehicles are available now?"
> "Where's the yellow Hummer2?"
> "How often do we service the Yugos?"
> "What vehicles are currently out of service?"
> "What is the most frequently rented vehicle type?"
> "Where were the step vans on 9/11/2001?"
> "Do you think we need more 4 wheel drive vehicles?"[/color]

And those are exactly the types of things one would want to know... even if
nobody is asking me for it now, they will! I'm going to have to give your
reply a better look but I've read it a few times now and it looks good.

Clearly we need a vehicles table.
[color=blue]
> Vehicles (VehicleID*, Type)
> Type = Compact, Mini-Van, Full-Size, etc.[/color]

And a customers table.
[color=blue]
> Customers (CustomerID*, CustomerName)[/color]

And a historic record of the rentals.
[color=blue]
> Rentals (VehicleID*, CustomerID*, DateRented*, DateReturned,[/color]
TypeRequested)

This table is interesting. Let me try to recap... renting a vehicle creates
a new "rental" record, updates the "change in location" record (departing
the place it was at) and adds a new "change in locations" record (arriving
at the customer.) And returning a rental updates an existing rental record
(it's been returned), updates the "change in location" (departing the
customer) and adds a new "change in location" record (arriving at the lot)
again.
[color=blue]
> LocationsVehicl es (LocationID*, VehicleID*, DateArrived*, DateDeparted)[/color]

And just to confirm, you are operating under the rule that CustomerId (along
with all the other locations) are unique to the system right? No big deal
but I don't think "In Transit" is a location.
[color=blue]
> Locations (LocationID*, Type)
> Type = Rental Lot, Service Station, Body Shop, In Transit, etc.[/color]
[color=blue]
> Question: Where are all the vehicles, disregarding rental status.[/color]
[color=blue]
> SELECT
> V.VehicleID,
> V.Type,
> L.LocationID,
> L.LocationType,
> LV.DateArrived
> FROM Vehicles V INNER JOIN (Locations L
> INNER JOIN LocationsVehicl es LV
> ON L.LocationID = LV.LocationID)
> ON V.VehicleID = LV.VehicleID
> WHERE LV.DateDeparted Is Null;[/color]

That query can be further limited by VehicleId, VehicleType, LocationId and
LocationType which makes it very useful. I think it can do the
LocationOfUnRen tedVehicles by limiting the type to "Rental Lot" or by
excluding the "Customer" type. And it can list OutStandingRent als as well
by limiting the type to "Customer" and joining the results to the rental
details and customer tables.

The "key" is the DateDeparted column. The Null indicates this is the last
location the vehicle was transferred to. That and the composite Locations
table which must contain every location a vehicle can be at.

I think this works... thanks again for all your work.

Tom