Best way to model hybrid single/multiple logical database

[Steve]

Re: Best way to model hybrid single/multiple logical database

On Sun, 4 Jan 2004 09:52:35 -0000, "Mike MacSween"
<mike.macsween. nospam@btintern et.com> wrote:
[color=blue]
>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>[color=green]
>> Basic entities: garages, customers, cars
>>
>> The customers park in only one garage at a time (business rule or
>> entity attribute?).[/color]
>
>What do you mean, that a customer is 'allocated' to one garage at any
>particular time?
>[color=green]
>> The cars table lists the cars for each customer, but have
>> attributes that are restricted by which garage the car is parked
>> in.[/color]
>
>So it's one to many, customers to cars? This 'attributes are restricted...'
>thing I can't make sense of. How can the attributes of a car entity be
>governed by where it is parked? Perhaps an example would help.
>[color=green]
>> Is the Garage an attribute of the car or an attribute of the
>> Customer?[/color]
>
>Like Rick says, you probably need a 'Rental' entity.
>[/color]

Clearly need a "Rental" entity, which identifies the car, garage and a
time period. There should be no duplicate amount cars/time periods -
car can only be parked once during a time. If the time period is not
consistent among garages (e.g. monthly), this can cause a problem.

Customers are related to cars ... one to many.

Steven

[David W. Fenton]

Re: Best way to model hybrid single/multiple logical database

rkc@yabba.dabba .do.rochester.r r.nope (rkc) wrote in
<BNKJb.110487$J W3.108748@twist er.nyroc.rr.com >:
[color=blue]
>
>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>news:9465CA42F dfentonbwayneti nvali@24.168.12 8.74...[color=green]
>> mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
>> <3ff73428$0$528 88$5a6aecb4@new s.aaisp.net.uk> :
>>[color=darkred]
>> >"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in
>> >message
>> >
>> >> Is the Garage an attribute of the car or an attribute of the
>> >> Customer?
>> >
>> >It's an entity.[/color]
>>
>> That's not what I meant. Of course, it's an entity. But is its
>> relation to the car or to the customer?[/color]
>
>Would not Car, Customer and Garage be attributes of a Rental
>relation? That is their indentifiers would be.[/color]

By identifiers, you mean CarID + CustomerID + GarageID?

I can't see the utility in this because it allows a single car to
belong to more than one customer. While this is possible in the
real world, from the point of view of the way monthly rental
contracts work, it's not. So, Cars only have one owner (i.e.,
Customer), so having CustomerID in that table is not helpful.

Now, the one advantage of having a rental relation table is that
you could have a single car in more than one garage, which is
theoretically possible (the person who lives in Brooklyn may have a
rental in both the Brooklyn garage and the Manhattan garage for a
single automobile).

That's a new wrinkle I hadn't thought of, and one that hadn't come
up because I'd not allowed a customer to rent space from more than
one garage.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Re: Best way to model hybrid single/multiple logical database

mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
<3ff7e264$0$528 83$5a6aecb4@new s.aaisp.net.uk> :
[color=blue]
>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>[color=green]
>> Basic entities: garages, customers, cars
>>
>> The customers park in only one garage at a time (business rule
>> or entity attribute?).[/color]
>
>What do you mean, that a customer is 'allocated' to one garage at
>any particular time?[/color]

"Allocated? " I'm simply saying that the client specified that a
customer never has rental agreements with more than one garage.

In truth, I'm not sure this was ever explicitly discussed. If it
had been, I don't think I would have put GarageID in tblCustomers.
[color=blue][color=green]
>> The cars table lists the cars for each customer, but have
>> attributes that are restricted by which garage the car is parked
>> in.[/color]
>
>So it's one to many, customers to cars? This 'attributes are
>restricted.. .' thing I can't make sense of. How can the attributes
>of a car entity be governed by where it is parked? Perhaps an
>example would help.[/color]

The valid attributes are restricted by the Garage. For instance,
some garages don't have any daily-only rates. Some garages have no
place for parking motorcycles, so the rates for those don't apply
to cars parked in particular garages. This is an exact analog to
Steve's original situation, where the choices for data in the
invoice detail were restricted by which company the invoice header
was billed by.
[color=blue][color=green]
>> Is the Garage an attribute of the car or an attribute of the
>> Customer?[/color]
>
>Like Rick says, you probably need a 'Rental' entity.[/color]

To make for a perfectly flexible schema, yes. But his suggestion
didn't make sense, either, because it allows multiple owners of one
car. While such a thing exists in reality (I guess? Now that I
think of it, I'm not sure that such a thing *does* really exist),
it does not exist in a rental contract -- one entity rents the
parking space.

Once the CustomerID is taken out of the Rental entity, the addition
of that entity really gains only the ability to place a single car
in more than one garage. I've seen the data for this app's original
client, and despite there being 30 different garages involved
spread geographically over 4 boroughs of NYC, there are no
customers listed in two different garages during the same time
period (because I put GarageID in the customer record, this was a
requirement of the app). This reflects the business's operating
practice going back to 1996 (the app went live on the first test
garage in summer 1997, but data dating back to 1996 was pre-loaded
into it).

Designing it now, I'd definitely omit the GarageID from the
customer table. This would allow for a single customer to have two
cars, one parked in one garage, and the other parked in another. It
would *not* allow for parking a single car in more than one garage
(the rental entity would be required for that). I'm not sure I'd
bother with that.

I've had all sorts of varying requirements for various customers
since the original who've purchased this app, but none has asked
for either of these scenarios. Indeed, none but the original
customer had more than one parking garage to manage in the first
place, so all the alterations have been around the handling of
customer attributes, distinguishing cars and handling various kinds
of rates and add-on charges.

If there were actually rental agreements involved that had
attributes that needed to be recorded in this application, I'd
introduce such a table. But the CarID wouldn't be in it -- there
would be a N:N join table between the Rental entity and the Car
entity. The garage would be an attribute of the Rental entity.

This has been an enlightening discussion for me as it has pointed
out some ways I could restructure the application to make it more
flexible.

Of course, I'm not going to do anything, since there's no guarantee
that I'd ever get paid for doing that alteration.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Re: Best way to model hybrid single/multiple logical database

steve@nospam.co m (Steve) wrote in
<3ff8439e.12832 15@news.westnet .com>:
[color=blue]
>On Sun, 4 Jan 2004 09:52:35 -0000, "Mike MacSween"
><mike.macsween .nospam@btinter net.com> wrote:
>[color=green]
>>"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message[/color][/color]
[color=blue][color=green][color=darkred]
>>> Is the Garage an attribute of the car or an attribute of the
>>> Customer?[/color]
>>
>>Like Rick says, you probably need a 'Rental' entity.
>>[/color]
>
>Clearly need a "Rental" entity, which identifies the car, garage
>and a time period. There should be no duplicate amount cars/time
>periods - car can only be parked once during a time. If the time
>period is not consistent among garages (e.g. monthly), this can
>cause a problem.
>
>Customers are related to cars ... one to many.[/color]

This is all very nice, but you folks are now engineering a
completely different application than the one my clients hired me
to build back in 1997.

But I've contemplated trying to make it better, and all these
points are of interest.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

Re: Best way to model hybrid single/multiple logical database


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:9466CEE92d fentonbwaynetin vali@24.168.128 .86...[color=blue]
> mike.macsween.n ospam@btinterne t.com (Mike MacSween) wrote in
> <3ff7e264$0$528 83$5a6aecb4@new s.aaisp.net.uk> :[color=green]
> >Like Rick says, you probably need a 'Rental' entity.[/color]
>
> To make for a perfectly flexible schema, yes. But his suggestion
> didn't make sense, either, because it allows multiple owners of one
> car. While such a thing exists in reality (I guess? Now that I
> think of it, I'm not sure that such a thing *does* really exist),
> it does not exist in a rental contract -- one entity rents the
> parking space.[/color]

I'm involved in a business partnership where vehicles are owned
by and registered to both partners. So, such a thing does exist.

That said, I bungled my point when I spoke of identifiers because
I was suggesting a conceptual relation, not a physical model. The
relation is a customer owns a car and rents a garage. It could be
extended to a customer that lives at an address owns a car and
rents a garage. It's not all modeled using one table, but it seems
to suggest a Rental agreement to me.