table structure problem

[rkc]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:Kbqbb.1347 7$u67.7611@twis ter.nyc.rr.com. ..[color=blue]
> "rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...[/color]
[color=blue]
> And just to confirm, you are operating under the rule that CustomerId[/color]
(along[color=blue]
> 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]

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]

Re: table structure problem

"rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...
[color=blue]
> The attributes with the asterisks make up the primary key. I envisioned
> customerID and locationID as system generated unique values.[/color]

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.
[color=blue]
> Somebody needs to transport it back to Syracuse.
> Where is it while it's on it's way back? In Transit.[/color]

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]

Re: table structure problem

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

[snip][color=blue]
>Hi Mike... I don't follow this, when did libraries stop tracking which books
>you've borrowed?[/color]

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.
[color=blue]
>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]

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.
[color=blue][color=green]
>> A customer is not a lot.[/color]
>
>That seems obvious. I'm still don't get your point.[/color]

What do database designers do with different things? We make them
into different tables, or we put them into different columns.
[color=blue][color=green]
>> 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][/color]
[snip][color=blue]
>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]

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.
[color=blue]
>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[/color]
[snip]

Check the vehicle disposition table, of course.

--
Mike Sherrill
Information Management Systems

[Tom Leylan]

Re: table structure problem

"Mike Sherrill" <MSherrill@comp userve.com> wrote...[color=blue]
> On Fri, 19 Sep 2003 16:29:49 GMT, "Tom Leylan" wrote:[color=green]
> >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[/color][/color]
(if[color=blue][color=green]
> >any) you have it on. That you have it is it's "location" right?[/color]
>
> 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.[/color]

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.
[color=blue]
> Check the vehicle disposition table, of course.[/color]
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]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:fbrbb.8067 $lZ6.2505759@tw ister.nyc.rr.co m...[color=blue]
> "rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...
>[color=green]
> > The attributes with the asterisks make up the primary key. I envisioned
> > customerID and locationID as system generated unique values.[/color]
>
> 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.
>[color=green]
> > Somebody needs to transport it back to Syracuse.
> > Where is it while it's on it's way back? In Transit.[/color]
>
> 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"[/color]
there[color=blue]
> 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[/color]
create[color=blue]
> a repair record using a CustomerId, etc.[/color]

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]

Re: table structure problem

"rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...
[color=blue]
> Well, I'm not as happy with the structure I outlined as you seem to be.
> There are some fundamental things I would change.[/color]

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]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:2JNbb.1038 0$lZ6.3002665@t wister.nyc.rr.c om...[color=blue]
> "rkc" <rkc@yabba.dabb a.do.rochester. rr.com> wrote...
>[color=green]
> > Well, I'm not as happy with the structure I outlined as you seem to be.
> > There are some fundamental things I would change.[/color]
>
> If it's an improvement I'd be happy to listen. I'd prefer not to run into[/color]
a[color=blue]
> stumbling block a week from now :-)[/color]

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]

Re: table structure problem

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

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]

Re: table structure problem


"Tom Leylan" <gee@iamtiredof spam.com> wrote in message
news:Ocicb.1653 8$nU6.3552976@t wister.nyc.rr.c om...
[color=blue]
> 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[/color]
less[color=blue]
> 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[/color]
a[color=blue]
> customer it can only transfer to a lot (for instance.)
> was a well-structured model available.[/color]

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]

Re: table structure problem

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

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.
[color=blue]
> The reason for the VehiclesLots table is so that you can track the[/color]
history[color=blue]
> of the locations a vehicle was rented out from.[/color]

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

Thanks,
Tom

[Mike Sherrill]

Re: table structure problem

On Mon, 22 Sep 2003 21:46:06 GMT, "Tom Leylan"
<gee@iamtiredof spam.com> wrote:
[color=blue]
>Mike your time is much to valuable to spend devising semantic arguments...[/color]

I'm not devising semantic arguments. But I am talking about
semantics, because semantics are a big part of your problem.
[color=blue]
>everybody over the age of two pretty much knows that "you have it" is all
>the library cares about.[/color]

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.
[color=blue][color=green]
>> Check the vehicle disposition table, of course.[/color]
>
>Well that's why you're in charge of things.[/color]

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

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