By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

which approach? (cofused with tables)

P: n/a
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


Jan 4 '06 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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" <DP@hotmail.com> wrote in message
news:Se******************@newsfe2-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.

Jan 4 '06 #2

P: n/a
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
Jan 4 '06 #3

P: n/a
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
Jan 4 '06 #4

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

Jan 4 '06 #5

P: n/a
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*********@SeeSig.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" <DP@hotmail.com> wrote in message
news:Se******************@newsfe2-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.


Jan 4 '06 #6

P: n/a
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********@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Have a look at Access 2000 Power Programming by Scott Barker if you can
find it... he has one in his book.

Jan 4 '06 #7

P: n/a
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.
Jan 4 '06 #8

P: n/a
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
Jan 4 '06 #9

P: n/a
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" <DP@hotmail.com> wrote in message
news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
news:Se******************@newsfe2-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.

Jan 5 '06 #10

P: n/a
DP
where could i get that sample database?

thanx

devin

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
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" <DP@hotmail.com> wrote in message
news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
news:Se******************@newsfe2-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.

Jan 5 '06 #11

P: n/a
DP
u know when u rent out a video or game, do u refer to it as a number, or a
name/title?

because if u refer to it as a name, you would need to distinguesh between
copy 1, 2, 3, etc... this could get misleading, as some films have
trilogies, etc...?

have u got a private e mail address, as i have created the tables and
relationships, but would lile you to check them. ??

thanx
devin

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
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" <DP@hotmail.com> wrote in message
news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
news:Se******************@newsfe2-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.

Jan 5 '06 #12

P: n/a
Hi Devin

I hope you can understand that we cannot just take everyone's databases and
sort them out for your for free. Honestly, we would never get anything else
done if we did that. (Thanks for asking rather than just sending.)

The suggestion was that each Product is a particular copy of a title. If you
have a trilogy, that would be 3 different titles, and you could potentially
have several copies of each (so several products with the same title.)

northwind.mdb is the sample database that installs by default when you
install Access. Search for it on your hard disk.

--
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" <DP@hotmail.com> wrote in message
news:WS*******************@newsfe3-gui.ntli.net...
u know when u rent out a video or game, do u refer to it as a number, or a
name/title?

because if u refer to it as a name, you would need to distinguesh between
copy 1, 2, 3, etc... this could get misleading, as some films have
trilogies, etc...?

have u got a private e mail address, as i have created the tables and
relationships, but would lile you to check them. ??

thanx
devin

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
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" <DP@hotmail.com> wrote in message
news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
>> news:Se******************@newsfe2-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.


Jan 5 '06 #13

P: n/a
DP
tanx.

sorry, i aint gonna send it now, i think i clocked it.

i've created 5 tables, and have got a film and games rental table. (for
each).
so i've got, customer, films, games, film rentals, game rentals.

the rental tables hold, when the rent went, and when it is due back.
the film and games tables, hold information on each individual film,
including multiple copies. (each copy has its own film/game id, but this
creates multiple copies of the same name in the table. E.g. 5 entries of
Fifa 06 just on 1 format. e.g. ps2) but i guess thats how it is anyway.

so now when the user wants to rent a film, he simply has to look up the
film, and insert its film id, and then the film will be rented.

the next bit, is to automate the renting process. (e.g. use a button to
rent, instead of remembering long numbers to identify the film/game). any
ideas how i do that?

thanx for all the help. i appreciate it.

devin

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

I hope you can understand that we cannot just take everyone's databases and sort them out for your for free. Honestly, we would never get anything else done if we did that. (Thanks for asking rather than just sending.)

The suggestion was that each Product is a particular copy of a title. If you have a trilogy, that would be 3 different titles, and you could potentially have several copies of each (so several products with the same title.)

northwind.mdb is the sample database that installs by default when you
install Access. Search for it on your hard disk.

--
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" <DP@hotmail.com> wrote in message
news:WS*******************@newsfe3-gui.ntli.net...
u know when u rent out a video or game, do u refer to it as a number, or a name/title?

because if u refer to it as a name, you would need to distinguesh between copy 1, 2, 3, etc... this could get misleading, as some films have
trilogies, etc...?

have u got a private e mail address, as i have created the tables and
relationships, but would lile you to check them. ??

thanx
devin

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
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" <DP@hotmail.com> wrote in message
news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
>> news:Se******************@newsfe2-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.



Jan 5 '06 #14

P: n/a
Probably best for you to continue with what you are comfortable doing at
this stage.

You will eventually discover that the structure you have does not cope well
with tracking how many copies you have of a title, customers hiring multiple
films at one time, or trying to hire both films and games to the same client
at the same time.

In answer to your new question, a combo box might help.

--
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" <DP@hotmail.com> wrote in message
news:jT******************@newsfe1-gui.ntli.net...
tanx.

sorry, i aint gonna send it now, i think i clocked it.

i've created 5 tables, and have got a film and games rental table. (for
each).
so i've got, customer, films, games, film rentals, game rentals.

the rental tables hold, when the rent went, and when it is due back.
the film and games tables, hold information on each individual film,
including multiple copies. (each copy has its own film/game id, but this
creates multiple copies of the same name in the table. E.g. 5 entries of
Fifa 06 just on 1 format. e.g. ps2) but i guess thats how it is anyway.

so now when the user wants to rent a film, he simply has to look up the
film, and insert its film id, and then the film will be rented.

the next bit, is to automate the renting process. (e.g. use a button to
rent, instead of remembering long numbers to identify the film/game). any
ideas how i do that?

thanx for all the help. i appreciate it.

devin

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

I hope you can understand that we cannot just take everyone's databases

and
sort them out for your for free. Honestly, we would never get anything

else
done if we did that. (Thanks for asking rather than just sending.)

The suggestion was that each Product is a particular copy of a title. If

you
have a trilogy, that would be 3 different titles, and you could

potentially
have several copies of each (so several products with the same title.)

northwind.mdb is the sample database that installs by default when you
install Access. Search for it on your hard disk.

"DP" <DP@hotmail.com> wrote in message
news:WS*******************@newsfe3-gui.ntli.net...
>u know when u rent out a video or game, do u refer to it as a number, or a > name/title?
>
> because if u refer to it as a name, you would need to distinguesh between > copy 1, 2, 3, etc... this could get misleading, as some films have
> trilogies, etc...?
>
> have u got a private e mail address, as i have created the tables and
> relationships, but would lile you to check them. ??
>
> thanx
> devin
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:43***********************@per-qv1-newsreader-01.iinet.net.au...
>> 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.
>>
>> "DP" <DP@hotmail.com> wrote in message
>> news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
>> >> news:Se******************@newsfe2-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.

Jan 5 '06 #15

P: n/a
DP
how would a combo box help?

u mean lisitng all the films/games in there. and then clicking an 'add'
button?
are there any other ways?
thanx
dev
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Probably best for you to continue with what you are comfortable doing at
this stage.

You will eventually discover that the structure you have does not cope well with tracking how many copies you have of a title, customers hiring multiple films at one time, or trying to hire both films and games to the same client at the same time.

In answer to your new question, a combo box might help.

--
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" <DP@hotmail.com> wrote in message
news:jT******************@newsfe1-gui.ntli.net...
tanx.

sorry, i aint gonna send it now, i think i clocked it.

i've created 5 tables, and have got a film and games rental table. (for
each).
so i've got, customer, films, games, film rentals, game rentals.

the rental tables hold, when the rent went, and when it is due back.
the film and games tables, hold information on each individual film,
including multiple copies. (each copy has its own film/game id, but this
creates multiple copies of the same name in the table. E.g. 5 entries of
Fifa 06 just on 1 format. e.g. ps2) but i guess thats how it is anyway.

so now when the user wants to rent a film, he simply has to look up the
film, and insert its film id, and then the film will be rented.

the next bit, is to automate the renting process. (e.g. use a button to
rent, instead of remembering long numbers to identify the film/game). any ideas how i do that?

thanx for all the help. i appreciate it.

devin

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

I hope you can understand that we cannot just take everyone's databases

and
sort them out for your for free. Honestly, we would never get anything

else
done if we did that. (Thanks for asking rather than just sending.)

The suggestion was that each Product is a particular copy of a title. If
you
have a trilogy, that would be 3 different titles, and you could

potentially
have several copies of each (so several products with the same title.)

northwind.mdb is the sample database that installs by default when you
install Access. Search for it on your hard disk.

"DP" <DP@hotmail.com> wrote in message
news:WS*******************@newsfe3-gui.ntli.net...
>u know when u rent out a video or game, do u refer to it as a number,
or a
> name/title?
>
> because if u refer to it as a name, you would need to distinguesh

between
> copy 1, 2, 3, etc... this could get misleading, as some films have
> trilogies, etc...?
>
> have u got a private e mail address, as i have created the tables and
> relationships, but would lile you to check them. ??
>
> thanx
> devin
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:43***********************@per-qv1-newsreader-01.iinet.net.au...
>> 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. >>
>> "DP" <DP@hotmail.com> wrote in message
>> news:xX******************@newsfe5-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*********@SeeSig.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" <DP@hotmail.com> wrote in message
>> >> news:Se******************@newsfe2-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.


Jan 5 '06 #16

P: n/a
You can set the Column Widths property of the combo so that the "long
numbers" column is zero-width, and it shows the meaningful text (such as the
title of the movie rather than the autonumber.)

--
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" <DP@hotmail.com> wrote in message
news:gI*******************@newsfe6-win.ntli.net...
how would a combo box help?

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

In answer to your new question, a combo box might help.

"DP" <DP@hotmail.com> wrote in message
news:jT******************@newsfe1-gui.ntli.net...
> tanx.
>
> the next bit, is to automate the renting process. (e.g. use a button
> to
> rent, instead of remembering long numbers to identify the film/game).

Jan 6 '06 #17

P: n/a
DP
oh. so when u click add, it adds the film id, but you only see the title. is
that wat u mean?

thanx

devin

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
You can set the Column Widths property of the combo so that the "long
numbers" column is zero-width, and it shows the meaningful text (such as the title of the movie rather than the autonumber.)

--
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" <DP@hotmail.com> wrote in message
news:gI*******************@newsfe6-win.ntli.net...
how would a combo box help?

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

In answer to your new question, a combo box might help.

"DP" <DP@hotmail.com> wrote in message
news:jT******************@newsfe1-gui.ntli.net...
> tanx.
>
> the next bit, is to automate the renting process. (e.g. use a button
> to
> rent, instead of remembering long numbers to identify the film/game).


Jan 6 '06 #18

P: n/a
Yep.

--
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" <DP@hotmail.com> wrote in message
news:J3*******************@newsfe4-win.ntli.net...
oh. so when u click add, it adds the film id, but you only see the title.
is
that wat u mean?

Jan 6 '06 #19

This discussion thread is closed

Replies have been disabled for this discussion.