1

I am creating schema of tables. But not able to understand best way to do it.

enter image description here

Following are the tables:

  1. Car Model - It contains all car models with types. For eg. Mercedes(brand) - GLA 250(model) - Sedan(type)
  2. Car Panel - It will contain all car panels. For eg. Fender(panel name)
  3. Car Panel Price By Type - It will contain car panel price by type. For eg. Sedan(type) - Fender(car panel id) - 2500(price)
  4. Car Panel Price By Model - It will contain car panel price by Model. For eg. Mecedes GLA 250(car model id) - Fender(car panel id) - 2700(price)
  5. Booking Package - It will contain all the order related fields. For eg. Order Id - Mercedes GLA 250 (car model id)
  6. Booking Package Panels - It will contain all the panels in order along with their price. For eg. Order id - Car Part Price By Model - Car Part Price By Type

Here, It will contain car panel price by model if it exists other wise car panel price by type. Because it can be possible that for some models, we don't add the price and use the type level pricing.

Another way to store Order panels.

For eg. Order id - Fender (panel id) - 2500(price from model or type)

Please suggest which is better way to design this order panel table.

  • How many tables include GLA 250? It should not be more than 1. Read about "normalization". – Rick James Apr 22 '16 at 3:29
  • So far only 1 table(CarModel). All other tables have foreign key of that table. – Anuj Apr 22 '16 at 3:48
  • OK; it was unclear from you list of tables. – Rick James Apr 22 '16 at 3:51
1

This is an impressive ERD. Are you sure you want to have every table inherit from CreatedAtAbstractBase?

The tables CarTypePanelPrice and CarModelPanelPrice are almost the same, differing only by car_model identifier. I would have the car_model field be optional (NULLable) and merge both tables. You'll know if one record is one type or the other if that field is NULL or NOT NULL. The orders/bookings seem alright.

  • CreatedAtAbstractBase is Abstract class in Python. It is used so that every model will have a created and update date. – Anuj Apr 19 '16 at 7:20
  • You may want to add the tag python as it seems to be relevant to your design then :) – Ziggy Crueltyfree Zeitgeister Apr 19 '16 at 7:24
  • Also, there is already a table(CarModel) which has mapping of car model and type. Will it be a good idea to have another table which will have foreign key of CarModel and has type also? – Anuj Apr 19 '16 at 7:27
  • @Anuj can you rephrase the question? What do you mean by CarModel having a mapping of car model? Isn't that what the table is for? A good idea having another table of what, and for having what? – Ziggy Crueltyfree Zeitgeister Apr 19 '16 at 7:35
  • 1
    There is one table CarModel which has model name and car type. Now the new tables CarTypePanelPrice is for pricing of panels by car types. CarModelPanelPrice is for pricing of panels by car models. If I combine those as per your answer, the new table will have both car type and CarModel with price of each panel. – Anuj Apr 19 '16 at 7:49

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.