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 VehicleLocations (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,loctype) REFERENCES
VehicleLocations (locationid,loctype))

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

--
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 VehicleLocations (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 VehicleLocations (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 VehicleLocations 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.lotid,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*@iamtiredofspam.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*******@compuserve.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*@iamtiredofspam.com> wrote in message
news:1a****************@twister.nyc.rr.com...

"Mike Sherrill" <MS*******@compuserve.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.

[Tom Leylan]

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

"Tom Leylan" <ge*@iamtiredofspam.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*@iamtiredofspam.com> wrote in message
news:kd******************@twister.nyc.rr.com...

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

"Tom Leylan" <ge*@iamtiredofspam.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.dabba.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*@iamtiredofspam.com> wrote in message
news:Zp********************@twister.nyc.rr.com...

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)

LocationsVehicles (LocationID*, VehicleID*, DateArrived*, DateDeparted)

Customers (CustomerID*, CustomerName)

[/Tables]

[Views]

CurrentVehicleLocations
Question: Where are all the vehicles, disregarding rental status.
Also used in LocationOfUnRentedVehicles view

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

OutStandingRentals
Question: What vehicles are currently out as rentals
and who rented them?
Also used in LocationOfUnRentedVehicles view.
Also used in CurrentStatusOfVehicles 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;

LocationOfUnRentedVehicles
Question: Where are the unrented vehicles?
Also use in CurrentStatusOfVehicles view

SELECT CurrentVehicleLocations.*
FROM CurrentVehicleLocations
WHERE CurrentVehicleLocations.VehicleID NOT IN
(SELECT VehicleID FROM OutStandingRentals);

CurrentStatusOfVehicles

SELECT LocationsOfUnRentedVehicles.*
FROM LocationsOfUnRentedVehicles
UNION
SELECT OutStandingRentals.*
FROM OutStandingRentals;

[/Views]

[Tom Leylan]

"rkc" <rk*@yabba.dabba.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.
LocationsVehicles (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 LocationsVehicles 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
LocationOfUnRentedVehicles by limiting the type to "Rental Lot" or by
excluding the "Customer" type. And it can list OutStandingRentals 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*@iamtiredofspam.com> wrote in message
news:Kb******************@twister.nyc.rr.com...

"rkc" <rk*@yabba.dabba.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.dabba.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*@iamtiredofspam.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*******@compuserve.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*@iamtiredofspam.com> wrote in message
news:fb********************@twister.nyc.rr.com...

"rkc" <rk*@yabba.dabba.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.

[Tom Leylan]

"rkc" <rk*@yabba.dabba.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*@iamtiredofspam.com> wrote in message
news:2J*********************@twister.nyc.rr.com...

"rkc" <rk*@yabba.dabba.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.dabba.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*@iamtiredofspam.com> wrote in message
news:Oc*********************@twister.nyc.rr.com...

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.dabba.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*@iamtiredofspam.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