5

enter image description here

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended?

  • Each body type has its own table
  • Each car in each body type table can has several features

As you can see from my "schema", each car can have several features but I need to connect features table to several car body types tables (many to many relationships). I've thought about it for several days but Im still not sure how to achieve this.

Why all body types are in separate tables? This seems to follow normalization rules, it should also speed up queries because I will never have to query several body types at once.

Is it possible or should I rethink it? Should I just make another table about body types and combine all the cars? I expect to have a maximum of 1M entries (all body types combined). There's going to be a lot more reads than writes.

Improve this question
0

1 Answer 1

Reset to default
6

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .

Improve this answer
2
  • Is supertype-subtype model viable for high-traffic very read-heavy app? Let's say there's a list view: I would need to query data from supertype and also from subtype. However if there's a single item view, I would need to query data from supertype, subtype and features.. Im sorry if this is stupid question, this is my first try with database design..
    – Oscar
    May 1, 2016 at 18:01
  • 2
    Generally, DBMS are highly optimized for read-heavy data from multiple tables scenario. Frequently requested data will be cashed in memory and plus having proper indices will make joining multiple tables really fast.
    – Serg
    May 1, 2016 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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