Question :
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.
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.