I need to design schema for a project that involves two things.
- providing services ( realted to vechicles)
- Also sells insurance , spare parts and maybe other types also can be added later
For each type of item order processing might be different, so I need to know the item type before order processing.
Also I need to know which type of item is in the cart so I can join to that table accordingly.
I also have aUsedCouponstable which containsitem_idandcoupon_id. Here also I need to know the item type.
same forcartItemsandorders
So far I can think of two approaches, but I don't think they are correct. I will explain below.
1. There will be tables for each items insurances spareParts services etc.
One more table to store types.
itemTypes
--------------------
id
type_name
now usedCoupons, cartItems and orders will contain a item_type_id along with item_id.
But here, I won't be able to join tables. As they depend on item _types
2. Putting item_type_id in just each items: insurances, spareParts services etc. ,
and putting only item_id into usedCoupons, cartItems< and orders tables.
But in this case I have to make a parent for all services.
itemParent
---------------------
id
name
description
item_id
item_type_id
Now again joining is painful here too.
I will join the cartItems to items, but for details again look for it's type before joining to a particular table.
For both approaches, I don't think they are right. There might be some other solution, If someone could help.