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

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

If it's an improvement I'd be happy to listen. I'd prefer not to run into a
stumbling block a week from now :-)

Tom

[rkc]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:2J******** *************@t wister.nyc.rr.c om...

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

Well, I'm not as happy with the structure I outlined as you seem to be.
There are some fundamental things I would change.
If it's an improvement I'd be happy to listen. I'd prefer not to run into

a stumbling block a week from now :-)

Going back to your original post where you had Cars, Lots and
Customers:

Vehicles (VehicleID*, Type, etc.)
Lots (LotID*, Address, etc.)
Customers (CustomerID*, etc.)

Learning from Mike Sherill's input:

VehicleEvents (EventID*, EventType*, VehicleID*, StartDate*, EndDate)
At this point you have one EventType - Rental.
Other possible events: Service, In Transit, Sale, etc.

VehicleLots (VehicleID, LotID, DateIn, DateOut)
Lot where vehicle is currently based

RentalDetails (EventID, VehicleID, CustomerID, etc.)
The query to answer where all vehicles currently are is basically the same
as before. What I hope this does is clear up why a CustomerID has to
be unique only in the context of the Customer table. It does not identify
where a vehicle is at the moment, just who it was rented to if it happens
to be rented at the moment.

[Tom Leylan]

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

The query to answer where all vehicles currently are is basically the same
as before. What I hope this does is clear up why a CustomerID has to
be unique only in the context of the Customer table. It does not identify
where a vehicle is at the moment, just who it was rented to if it happens
to be rented at the moment.

Well I'm going to have to think the entire thing through again. I don't
quite see it as the vehicle is "based" at a lot. The lot isn't more or less
significant than the customer or any other location, it is just one place
the vehicle can be at. Then certain rules govern what transfers can be
made. From a lot it can transfer to another lot or to a customer but from a
customer it can only transfer to a lot (for instance.)

Different data needs to be kept depending upon the type of exchange...
"rental", "return", "service", "sale", "transfer" but generally speaking
these all change the location. I think "location" is a property of any
physical object (in this case the vehicle.) If we painted the car it's
color would change. If we didn't care when it happened we could just change
the color property in the vehicle record but if we did care we would need to
retain the details in another table. And I'll go out on a limb and guess
that "if it wasn't a pain" we would care enough to keep the details. The
details might be significant later when we don't have them.

As a side note (perhaps you have some insights into this) I find it
extraordinarily curious given how long relational databases, software
development, and the need to solve certain "standard" problems have been
around that there aren't more "engineered solutions" to these things. By
that I mean a solution that some majority of developers have agreed upon
"works."

These domain-related solutions are duplicated all over the place, it could
be cars, it could be video tapes, I suppose it would be the same for
tracking for prisoners in the prison system. It sure would be nice if there
was a well-structured model available.

Thanks again for all your input on the subject, I really think you went out
of your way to help.
Tom

[rkc]

"Tom Leylan" <ge*@iamtiredof spam.com> wrote in message
news:Oc******** *************@t wister.nyc.rr.c om...

Well I'm going to have to think the entire thing through again. I don't
quite see it as the vehicle is "based" at a lot. The lot isn't more or less significant than the customer or any other location, it is just one place
the vehicle can be at. Then certain rules govern what transfers can be
made. From a lot it can transfer to another lot or to a customer but from a customer it can only transfer to a lot (for instance.)
was a well-structured model available.

The rental lot is the 'default' location of a vehicle. If the vehicle
doesn't
have a record in the events table with a null end date then it is by default
at the corresponding lot as indicated by a record with a null dateout
field in the VehiclesLots table. The reason for the VehiclesLots table
is so that you can track the history of the locations a vehicle was rented
out from. If you don't need or want that information then stick a LotID
in the Vehicles table and update it when the vehicle is moved to a different
rental lot. That will tell you where the vehicle is being rented from now,
but you won't know for certain where it was rented from a week ago.

BTW, I have a VehicleID in the RentalDetails table that isn't necessary.
That's determined by a join to the Events table via the EventID.

[Tom Leylan]

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

The rental lot is the 'default' location of a vehicle. If the vehicle
doesn't have a record in the events table with a null end date then
it is by default at the corresponding lot as indicated by a record
with a null dateout field in the VehiclesLots table.
That will prove to be a poor assumption. <grin> I have a hard time
explaining the concept to people but nothing is ever static and I try (not
always successfully) to provide room for growth. If you assume immediately
that the vehicle must be in a lot if you can't find it anywhere else you
curtail the ability to have vehicles "on order" for instance.

You already mentioned how "in transit" would be good. Then the company
could add vehicles to the vehicle table that are pending delivery. That's
immediately useful so they don't order too many of one type, don't get
overly concerned with a shortage at the moment, etc., etc. They aren't
assigned to a lot until they are delivered since which lot they will go to
may not be known until delivery.

There is absolutely nothing wrong from establishing a "lot-based' view but I
think it would be nice to track things from the vehicle's viewpoint. I
might be chasing my tail on this but I don't see why we would query the
vehicle for it's color, how many miles it has, (and if it had at GPS system)
"where are you now" but not consider querying it for whether it is in a lot
or a customer has it or anything else. We're probably saying the same thing
but in different words. I'd rather not think of it as defaulting but rather
that the record actually indicates it is at a lot just as the record is
checked to see if it is anywhere else.
The reason for the VehiclesLots table is so that you can track the history of the locations a vehicle was rented out from.
You'd never want to lose that information it's part of the invoice if
nothing else. It's a given that you need to know who, what, where, when,
and how much.
BTW, I have a VehicleID in the RentalDetails table that isn't necessary.
That's determined by a join to the Events table via the EventID.

Thanks,
Tom

[Mike Sherrill]

On Mon, 22 Sep 2003 21:46:06 GMT, "Tom Leylan"
<ge*@iamtiredof spam.com> wrote:

Mike your time is much to valuable to spend devising semantic arguments...
I'm not devising semantic arguments. But I am talking about
semantics, because semantics are a big part of your problem.
everybody over the age of two pretty much knows that "you have it" is all
the library cares about.
You'd think that everybody over the age of two would pretty much know
that saying "you have it" isn't the same as saying "It is in
Cleveland". In relational design, different sentences are roughly
equivalent to different predicates; different predicates imply
different tables and columns.

Check the vehicle disposition table, of course.

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

Because I'd look in the vehicle disposition table for vehicle
dispositions? Who wouldn't?
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.

Earlier, you wrote this:

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

Based on that, I'd have to say you clearly don't understand the
difference between "who has it" and "where is it".

--
Mike Sherrill
Information Management Systems