How to model ski rental equipment

database-design

I've spent a good amount at time at looking at related posts regarding products for rent but still have a few unanswered questions.

If we consider the just the requirements at hand which is how to model ski rental product packages and ski rental products, the scenario is this.

If we consider a ski rental package that consists of skis, poles, ski boots and insurance, number of days rental, how should I model this taking into account the addition of ski boots and insurance is optional and priced differently.

For example, a client may wish to rent skis for 6 days but a different client may want to rent the same skis with ski boots and insurance for 7 days. The pricing for each choice is dependent on the items within package as well as the duration. The pricing is typically on a sliding scale where the longer you rent, the better saving is made (when braking it down into a daily cost).

I've got the following model as a start :

customer
--------
customer_id    
customer_name

orders
------
order_id    
customer_id    
order_date    
order_type (purchase/rental)    

order_items
-----------
order_item_id    
order_id (FK in orders table)    
product_id (FK in product table)    
price    
discount

product
-------
product_id    
product_name    
sale_price (if product available for sale)    

rent_detail
-----------
order_id (FK in orders table)    
date_out    
date_return

product_rental_price_category
-----------------------------
product_rpc_id    
name (e.g. : standard, returning customer, student)

product_rental_price
--------------------
product_id (FK in product table)
product_rpc_id (FK in product_rental_price_category)    
number_of_days    
price

Now, the real problem I have is with the inclusion (or exclusion) of the optional parts within a package, such as boots, helmet or insurance.

Any thoughts would be greatly appreciated.

Best Answer

Most of that model looks very good to me.

I think the piece you're missing is the concept of a package.

I would make some products packages (or sets) of products.

product

product_id

product_name (could be "diamond package" rather than "ski")

sale_price (if product available for sale)

is_set (boolean indicator that this is a set, not a single item)

set_detail

set_detail_id (just a serial / autonumber)

set_product_id (references the product_id of the set)

set_item_id (references the product_id of the item within the set, e.g. ski or boot)

With that, I believe the rest of your model, including the complicated pricing, works.

Related Question