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

[Tom Leylan]

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

"Tom Leylan" <ge*@iamtiredof spam.com> wrote...

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.

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]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:kd******** **********@twis ter.nyc.rr.com. ..

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

"Tom Leylan" <ge*@iamtiredof spam.com> wrote...

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.

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.

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]

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

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.

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]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:Zp******** ************@tw ister.nyc.rr.co m...

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.

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]

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

Gosh you did a lot of work... thanks.

"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?"
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.
Vehicles (VehicleID*, Type)
Type = Compact, Mini-Van, Full-Size, etc.
And a customers table.
Customers (CustomerID*, CustomerName)
And a historic record of the rentals.
Rentals (VehicleID*, CustomerID*, DateRented*, DateReturned, 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.
LocationsVehicl es (LocationID*, VehicleID*, DateArrived*, DateDeparted)
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.
Locations (LocationID*, Type)
Type = Rental Lot, Service Station, Body Shop, In Transit, etc. Question: Where are all the vehicles, disregarding rental status. 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;

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

[rkc]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:Kb******** **********@twis ter.nyc.rr.com. ..

"rkc" <rk*@yabba.dabb a.do.rochester. rr.com> wrote... 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.

The attributes with the asterisks make up the primary key. I envisioned
customerID and locationID as system generated unique values.

Say a customer rents the yellow hummer in Syracuse, NY and drives it
to Chicago Illinois. One way. The people in Syracuse want it back
because it's a big money maker.

Somebody needs to transport it back to Syracuse.

Where is it while it's on it's way back? In Transit.

[Tom Leylan]

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

The attributes with the asterisks make up the primary key. I envisioned
customerID and locationID as system generated unique values.
They are system generated but in order to work they have to be "system"
unique not simply unique within a particular Id type. In order to be a
possible location the CustomerId has to be found in the Location table
right? It isn't enough to simply have a generic "customer" location.
Somebody needs to transport it back to Syracuse.
Where is it while it's on it's way back? In Transit.

Oh definitely but in order to track it in the location table it has to be
somewhere An "InTransit" type has to be assigned to a LocationId. It was
at a "rental lot" it is currently on it's way to another "rental lot" there
isn't any LocationId with an InTransit type.

It could be handled as a "transfer" much as a rental is but in a Transfers
table. A rental is an exchange between a lot and a customer and transfer
is an exchange between two lots. A repair (details in the Repairs table)
would be an exchange between a lot and a "repair station." The
RepairStationId would be found in the Locations table in the LocationId
column.

It works out well also because the LocationType can restrict meaningless
transactions... you can't create a rental with a RepairStationId , or create
a repair record using a CustomerId, etc.

[Mike Sherrill]

On Fri, 19 Sep 2003 16:29:49 GMT, "Tom Leylan"
<ge*@iamtiredof spam.com> wrote:

[snip]

Hi Mike... I don't follow this, when did libraries stop tracking which books
you've borrowed?
They didn't. Libraries record who checked out the books; they don't
record where the books are. The books *might* be at the home address
of the person who checked them out, but they might not be.
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?
No. If I check out a book, it means I have the book, and the library
doesn't. It doesn't mean the library knows where the book is.

A customer is not a lot.

That seems obvious. I'm still don't get your point.

What do database designers do with different things? We make them
into different tables, or we put them into different columns.

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.

[snip]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. :-)
You probably need to store different information about rentals than
you need to store about service. This suggests that information about
rentals belongs in a different table than information about service.
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

[snip]

Check the vehicle disposition table, of course.

--
Mike Sherrill
Information Management Systems

[Tom Leylan]

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

On Fri, 19 Sep 2003 16:29:49 GMT, "Tom Leylan" wrote:

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 (ifany) you have it on. That you have it is it's "location" right?
No. If I check out a book, it means I have the book, and the library
doesn't. It doesn't mean the library knows where the book is.

Mike your time is much to valuable to spend devising semantic arguments...
everybody over the age of two pretty much knows that "you have it" is all
the library cares about.
Check the vehicle disposition table, of course.

Well that's why you're in charge of things.

I always admire how newsgroup conversations can be carried on in such an
adult manner... like your general assumption that I could be confused about
the way a library book works. So many people wouldn't take the time to
explain how you could put a book on a shelf in the basement (for instance)
and how (contrary to what the average human might expect) the librarian
wouldn't "know."

Your correctly surmised the real topic of my original message. Thanks so
much for sharing.

[rkc]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:fb******** ************@tw ister.nyc.rr.co m...

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

The attributes with the asterisks make up the primary key. I envisioned
customerID and locationID as system generated unique values.
They are system generated but in order to work they have to be "system"
unique not simply unique within a particular Id type. In order to be a
possible location the CustomerId has to be found in the Location table
right? It isn't enough to simply have a generic "customer" location.

Somebody needs to transport it back to Syracuse.
Where is it while it's on it's way back? In Transit.

Oh definitely but in order to track it in the location table it has to be
somewhere An "InTransit" type has to be assigned to a LocationId. It was
at a "rental lot" it is currently on it's way to another "rental lot"

there isn't any LocationId with an InTransit type.

It could be handled as a "transfer" much as a rental is but in a Transfers
table. A rental is an exchange between a lot and a customer and transfer
is an exchange between two lots. A repair (details in the Repairs table)
would be an exchange between a lot and a "repair station." The
RepairStationId would be found in the Locations table in the LocationId
column.

It works out well also because the LocationType can restrict meaningless
transactions... you can't create a rental with a RepairStationId , or create a repair record using a CustomerId, etc.

Well, I'm not as happy with the structure I outlined as you seem to be.
There are some fundamental things I would change.

That's great though. If things are more clear for you now, go with it.