Movie Rental Design

[Guest]

I was wondering if anybody could provide some "heads-up" assistance before I proceed on some DB development

I am attempting to create a DB that allows a user to rent a piece of inventory for an alloted time period. Basically, I have 2 tables created, 1 each for the Inventory and the Renter. Additionally, I have created a Table that will handle the Transactions (i.e. When a renter rents an item and returns the item).

I created a system similar to this one before but I had to use a lot of code and that made things a bit difficult for others. Is there an easier way to allow the operator to select a renter, choose the item to be rented, and then record that transaction in the Transaction table? All I basically need is the TransID, Renter Name, Item Name and the date rented. I already have those fields in the table, but unsure of how to develop the form that will allow the operator to rent the item and then record another transaction when the item is returned.

Thanks

 

[Allen Browne]

You do not hire out movies. You hire a *copy* of the movie
You may have multiple copies of a movie, in different formats, and hired to
different people at any one time.

Therefore you need a structure like this:

Movie table (one record for each move that comes out):
- MovieID (AutoNumber, primary key)
- MoveName
- ReleaseDate

MoveCopy table (one record for each disc/tape you buy):
- MovieCopyID (AutoNumber, primary key)
- MovieID (which movie. Foreign key to Movie.MovieID)
- FormatID ("DVD", "tape", ...)
- DateAcquired (when you got this copy)
- Inactive (Yes/No. Yes when sold/lost/destroyed).

Client table (one record for each member):
- ClientID (AutoNumber, primary key)
- Surname (and other fields).

Hire table (one record for each time a customer hires movies):
- HireID (Autonumber, primary key)
- HireDate (Date of hiring)
- ClientID (who hired. Foreign key to Client.ClientID)

HireDetail table (one record for each movie copy in a hiring):
- HireDetailID (AutoNumber, primary key)
- HireID (which hiring. Foreign key to Hire.HireID)
- MovieCopyID (which tape/disk. Foreign key to MovieCopy.MovieCopyID)
- DueDate Date/Time. When this copy is due back from this hiring.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I was wondering if anybody could provide some "heads-up" assistance before

I proceed on some DB development.

I am attempting to create a DB that allows a user to rent a piece of

inventory for an alloted time period. Basically, I have 2 tables created, 1
each for the Inventory and the Renter. Additionally, I have created a Table
that will handle the Transactions (i.e. When a renter rents an item and
returns the item).

I created a system similar to this one before but I had to use a lot of

code and that made things a bit difficult for others. Is there an easier way
to allow the operator to select a renter, choose the item to be rented, and
then record that transaction in the Transaction table? All I basically need
is the TransID, Renter Name, Item Name and the date rented. I already have
those fields in the table, but unsure of how to develop the form that will
allow the operator to rent the item and then record another transaction when
the item is returned.