473,322 Members | 1,562 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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


Jan 4 '06 #1
18 1568
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Martin Euredjian | last post by:
I could use a shove in the right direction... I'm using the Dreamweaver MX suite to build a website for my business. At first I threw something together quickly just to get going. I now need...
3
by: Edward | last post by:
ASP.NET / VB.NET SQL Server 7.0 Our client has insisted that we change our established practice of building SQL in-line and move it all to SPROCs. Not a problem for 80% of the app. However,...
10
by: heromull | last post by:
We have an asp.net app with about 200 data entry forms. Customers may enter data into any number of forms. Each form's data is persisted in a corresponding sql table. When data entry is...
2
by: Randy | last post by:
I am trying to make the move from Approach to Microsoft Access and am having real problems with developing forms? Would someone please describe the Access concept of developing forms using...
34
by: Jeff | last post by:
For years I have been using VBA extensively for updating data to tables after processing. By this I mean if I had to do some intensive processing that resulted in data in temp tables, I would have...
15
by: Rob Meade | last post by:
Hi all, I have a databse which I'm pulling the data from for my ASP page. I have 4 tables, Course, Feature, Objective, and PreRequisite. The last three all contain a course product code and a...
5
by: kutty | last post by:
Hi All, I am loading data to a child table from a text file. the text files also contains data not referenced by parent key. while loading the data if one row fails to satisfies the constraint...
17
by: The Frog | last post by:
Hello everyone, I am working on an application that can build database objects in MS Access from text files. I suppose you could call it a backup and restore type routine. Accessing the...
2
by: rn5a | last post by:
A Form has a select list which is populated from a MS-Access database table. The DB table from where the select list is populated has 2 columns - CountryID & CountryName. When the Form is posted,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.