online reservation for products

generic

I'm trying to design a system that will act as a product database for concert sound and lighting equipment but also allow customers to reserve items in the database for set periods of time directly from the website. Note that no money will be exchanged at the reservation time, the user will receive a RENTAL ID number upon successful reservation and will pay upon pickup from the shop.

Below is what I have put together for a database schema. My main questions is what is the best way to associate multiple PRODUCTS to one RENTAL without knowing exactly how many items will be going on any given rental invoice?

Also, in general does this look like an overly bloated setup or does it appear to be robust enough to handle the type of system I'm looking to build?

CUSTOMERS

customer id
customer name
customer street
customer city
customer province
customer postcode
customer country
customer phone
customer email
customer since
customer last rental
customer login
customer password

PRODUCT CATEGORY

prodcat id
prodcat name
prodcat parent

PRODUCTS

product id
product category
product subcat
product manufacturer
product name
product details
product image
rental cost (cost per day)
product price (replacement cost)
product since

RENTALS

rental id
product id
current renter (use customer id for 'unavailable' or NULL if 'available')
datetime out
datetime in
rental cost
rental notes

Thanks in advance,

gen

dagon

generic;10938488 wrote:
Below is what I have put together for a database schema. My main questions is what is the best way to associate multiple PRODUCTS to one RENTAL without knowing exactly how many items will be going on any given rental invoice?

create a prod_rent table with
product id
rental id

generic

dagon;10938491 wrote:
create a prod_rent table with
product id
rental id

So what if someone had a rental with three items due at time X and another one on the same invoice due back at time Y? Maybe I'm just missing the relation between the product, rental and prod_rent tables...

dagon

add an invoice table

invoice id, product id, rental id, due date

may be worth having an invoice and and invoice items table.

generic

Thanks for the info, I'll give this a whirl. Failing everything else, I'll just hire someone to do it or look for a decent existing system to work with.

Cheers!