-1

I need to design schema for a project that involves two things.

  1. providing services ( realted to vechicles)
  2. 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 a UsedCoupons table which contains item_id and coupon_id. Here also I need to know the item type.
    same for cartItems and orders

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.

sujeet
  • 3,480
  • 3
  • 28
  • 60
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Feb 27 '20 at 06:47
  • 1
    This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Feb 27 '20 at 06:48

1 Answers1

0

Based on this answer , I think I can go with below schema

Items
--------------------
item_id (primary key)
name
description
price
item_type_id ( here foreign key, primary key for item types)
//it contains all the shared attributes in each type of items

Services
-----------------------
service_id (primary key)
item_id (foreign key, primary for Items)
....other attributes

SpareParts
---------------------
spare_part_id (primary_key)
item_id (foreign key, primary for Items)
....other attributes


Insurances
-----------------------
insurance_id (primary_key)
item_id (foreign key, primary for Items)
....other attributes

//Finally have a ItemTypes tables

ItemsTypes
---------------------
item_type_id (primary key)
item_type ( varchar, name of the item type : service, insurance)

Now, UsedCoupon CartItems and Orders will have only item_id

But still I need someone's attentions here, If someone could help I will appreciate it.

sujeet
  • 3,480
  • 3
  • 28
  • 60