1

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.

  • In rent_detail I'd have a date_due and date_returned - that way you can model late items. – Philᵀᴹ Jul 2 '14 at 13:32
  • +1 on separating date_due from date_returned. – Daniel Wilson Jul 2 '14 at 16:58
  • Thanks for the comment above. I did mean date_returned to mean date_due (but I named it badly). But your suggestion still holds as it will be useful to record the actual date returned. I'll include both as suggested. – Robin M Jul 3 '14 at 18:33
1

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.

  • Hi Daniel, thanks for showing me that solution, that seems to fit what I need perfectly. Nice and simple when you see it. – Robin M Jul 3 '14 at 19:43
  • Actually, upon further thought, this brings up another question. how can I model (perhaps in the order_items table), the 'options' chosen/purchased within the 'package'? Fo example, in a package with Skis, Ski Boots, Helmet and Insurance, each item is optional except for the Skis. – Robin M Jul 3 '14 at 20:19
  • Do those 'options' make for a different package? If not ... let me think. Are the products in those options also within the product table? If so ... I'd add another field to the set_detail table. Maybe ... 2 fields. is_optional bool default it true or false ... according to the business. Do they add boots, insurance, etc. or decline them? option_price numeric or possibly option_price_per_day – Daniel Wilson Jul 4 '14 at 14:58
  • Hi Daniel, the options don't really make for a different package but they do produce a different price. If we consider an example, a couple going skiing, the first person might need skis, boots and helmet. The second person being a better skier brings his own boots and helmet, so he goes for the same package but doesn't select the boots or helmet. He does go for the insurance though. So they both take the same package but with different options. – Robin M Jul 4 '14 at 18:57
  • I was thinking of modelling skis, boots, helmets and insurance all as 'products' although I've not done this in a previous project. In the set_detail table, an boldis_optionalbold field is a good idea, but I need to also store the selection made at time of booking... – Robin M Jul 4 '14 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.