which approach? (cofused with tables)

[DP]

hi,

i'm designing a video and games rental database.

i've got the customer table, with all the correct and relavant details.
i've got a films, and games table.

But i'm confused which way to link them.

e.g.
a custoemr can rent a video and a game, but do i have to have a seperate
'rental' table for each category, or 1 table, for both fo the rents?

or

do i have a rental table, (with just the id, and due dates,)
and have seperate video rental and game rental tables?

i've created an erd diagram, which one should i use? i have not attached it,
but i could if somone is willing to help me slove this little problem.

thanx

devin

[Allen Browne]

Use a Title table that contains all the titles you hire out (both games and
videos). Include a TitleType field, to distinguish which it is.

Of course, you might have multiple copies of one title. You don't hire
Titles to your customers, but instances (a particular disk, that probably
has a unique barcode on it.) You will therefore have a Product table which
contains everything you have for hire. It will have fields such as:
ProductID AutoNumber Primary key
TitleID relates to the Title table's primary key (what title
this is.)
PurchDate Date/Time When you bought this instance.
Inactive Yes/No Mark inactive if this copy is lost or
damaged.

You will then need the Hire table:
HireID AutoNumber Primary key
ClientID Relates to Client table's primary key (Who hired this.)
HireDate Date/Time When this hire was taken out.
and a HireDetail table (for all the items in this hiring):
HireDetailID AutoNumber primary key
HireID Number. Relates to primary key of Hire table.
ProductID Number What item was hired.
DueDate Date/Time When this item is due back.
Charge Currency How much the client is charged for hiring
this item.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DP" <[e-mail] > wrote in message
news:Se******** **********@news fe2-win.ntli.net...

i'm designing a video and games rental database.

i've got the customer table, with all the correct and relavant details.
i've got a films, and games table.

But i'm confused which way to link them.

e.g.
a custoemr can rent a video and a game, but do i have to have a seperate
'rental' table for each category, or 1 table, for both fo the rents?

or

do i have a rental table, (with just the id, and due dates,)
and have seperate video rental and game rental tables?

i've created an erd diagram, which one should i use? i have not attached
it,
but i could if somone is willing to help me slove this little problem.

[(PeteCresswell)]

Per DP:

hi,

i'm designing a video and games rental database.

Have you looked into what's available off-the-shelf?

Sounds like one of those bread-and-butter applications where you can get
something ready-made that does 90% of what you want for 10% of the cost of doing
it yourself.
--
PeteCresswell

[(PeteCresswell)]

Per DP:

i'm designing a video and games rental database.

One of the WROC How-To books for object-oriented programming uses video rental
as the subject of one of their examples.

My recollection is that the author's last name had an American Indian sound to
it - something like Lahota.

Maybe somebody else can recall the title.
--
PeteCresswell

[pietlinden]

Have a look at Access 2000 Power Programming by Scott Barker if you can
find it... he has one in his book.

[DP]

hi,

thanx for yor reply. i understand the tables you have described, but just
need a few things clearing up.

your suggesting that there only needs to be 1 table, for the videos and
games. , but a new table to distinguesh between the titles. because there
are 5 copies of each video and game.

now that i have read your reply, i am thinking of keeping my 2 tables for
video and games, but to create another 2 tables to link them as products.
and each rpoduct will be given an individual ID.

and the customer rents a product, not a title.

Why have you created a hire and a hiredetail table? why cant u just have the
one? .

the customer table relates to the hire table, the hire table relates to the
hiredetail table. the hiredetail table relates to the product table. the
product table relates to the title table. ??

thanx

devin

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:43******** *************** @per-qv1-newsreader-01.iinet.net.au ...

Use a Title table that contains all the titles you hire out (both games and videos). Include a TitleType field, to distinguish which it is.

Of course, you might have multiple copies of one title. You don't hire
Titles to your customers, but instances (a particular disk, that probably
has a unique barcode on it.) You will therefore have a Product table which
contains everything you have for hire. It will have fields such as:
ProductID AutoNumber Primary key
TitleID relates to the Title table's primary key (what title
this is.)
PurchDate Date/Time When you bought this instance.
Inactive Yes/No Mark inactive if this copy is lost or
damaged.

You will then need the Hire table:
HireID AutoNumber Primary key
ClientID Relates to Client table's primary key (Who hired this.)
HireDate Date/Time When this hire was taken out.
and a HireDetail table (for all the items in this hiring):
HireDetailID AutoNumber primary key
HireID Number. Relates to primary key of Hire table.
ProductID Number What item was hired.
DueDate Date/Time When this item is due back.
Charge Currency How much the client is charged for hiring this item.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DP" <[e-mail] > wrote in message
news:Se******** **********@news fe2-win.ntli.net...

i'm designing a video and games rental database.

i've got the customer table, with all the correct and relavant details.
i've got a films, and games table.

But i'm confused which way to link them.

e.g.
a custoemr can rent a video and a game, but do i have to have a seperate
'rental' table for each category, or 1 table, for both fo the rents?

or

do i have a rental table, (with just the id, and due dates,)
and have seperate video rental and game rental tables?

i've created an erd diagram, which one should i use? i have not attached
it,
but i could if somone is willing to help me slove this little problem.

[DP]

i found it, but its $22, i dont want to pay that, just to know what tables i
need. i'm just a little confused. i want to create it effeciently, so i'm
not wasting processing power.

r there any examples on the net anywhere?

thanx

devin

<pi********@hot mail.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Have a look at Access 2000 Power Programming by Scott Barker if you can
find it... he has one in his book.

[rkc]

(PeteCresswell) wrote:

Per DP:

i'm designing a video and games rental database.

One of the WROC How-To books for object-oriented programming uses video rental
as the subject of one of their examples.

My recollection is that the author's last name had an American Indian sound to
it - something like Lahota.

Professional Visual Basic 6 Business Objects is the title, but it's
probably way more than the op is looking for.

[(PeteCresswell)]

Per rkc:

Professional Visual Basic 6 Business Objects is the title, but it's
probably way more than the op is looking for.

Thanks. Also I fat-fingered the publisher's name.

WROC sb WROX.
--
PeteCresswell

[Allen Browne]

The HireDetail table exists in the hope that sometimes customers will rent
more than one DVD/game at a time. I would consider that to be a single
transaction, with a total amount that they pay at the time. You could treat
them as separate hirings I suppose, and handle each transaction separtely,
so the staff takes $2 for the first one and gives change, and then accepts
$2.95 for the next game the customer wants and gives change, and then ...

For an example of that concept, open the Northwind sample database. Open the
Orders form, and see how the order header can have many detail lines. To see
how these table fit together, choose Relationships on the Tools menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DP" <[e-mail] > wrote in message
news:xX******** **********@news fe5-win.ntli.net...

hi,

thanx for yor reply. i understand the tables you have described, but just
need a few things clearing up.

your suggesting that there only needs to be 1 table, for the videos and
games. , but a new table to distinguesh between the titles. because there
are 5 copies of each video and game.

now that i have read your reply, i am thinking of keeping my 2 tables for
video and games, but to create another 2 tables to link them as products.
and each rpoduct will be given an individual ID.

and the customer rents a product, not a title.

Why have you created a hire and a hiredetail table? why cant u just have
the
one? .

the customer table relates to the hire table, the hire table relates to
the
hiredetail table. the hiredetail table relates to the product table. the
product table relates to the title table. ??

thanx

devin

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:43******** *************** @per-qv1-newsreader-01.iinet.net.au ...

Use a Title table that contains all the titles you hire out (both games

and

videos). Include a TitleType field, to distinguish which it is.

Of course, you might have multiple copies of one title. You don't hire
Titles to your customers, but instances (a particular disk, that probably
has a unique barcode on it.) You will therefore have a Product table
which
contains everything you have for hire. It will have fields such as:
ProductID AutoNumber Primary key
TitleID relates to the Title table's primary key (what title
this is.)
PurchDate Date/Time When you bought this instance.
Inactive Yes/No Mark inactive if this copy is lost or
damaged.

You will then need the Hire table:
HireID AutoNumber Primary key
ClientID Relates to Client table's primary key (Who hired this.)
HireDate Date/Time When this hire was taken out.
and a HireDetail table (for all the items in this hiring):
HireDetailID AutoNumber primary key
HireID Number. Relates to primary key of Hire table.
ProductID Number What item was hired.
DueDate Date/Time When this item is due back.
Charge Currency How much the client is charged for

hiring

this item.

"DP" <[e-mail] > wrote in message
news:Se******** **********@news fe2-win.ntli.net...

>
> i'm designing a video and games rental database.
>
> i've got the customer table, with all the correct and relavant details.
> i've got a films, and games table.
>
> But i'm confused which way to link them.
>
> e.g.
> a custoemr can rent a video and a game, but do i have to have a
> seperate
> 'rental' table for each category, or 1 table, for both fo the rents?
>
> or
>
> do i have a rental table, (with just the id, and due dates,)
> and have seperate video rental and game rental tables?
>
> i've created an erd diagram, which one should i use? i have not
> attached
> it,
> but i could if somone is willing to help me slove this little problem.