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.
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_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_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.