0

i would like to know how to decide between different database-design solutions?

I guess best to describe my Question is to give an example.
Lets say we want to create a Database for Cars. Every Car has a number of Properties we want to save. There are a lot of Properties every Car has like:

Producer,
Model,
Color,
Age,...

But here are also Properties that are just found in a subcategory or in a small group of cars like:
Draw Bar,
Roof Rack,
Cargo area,
4 Wheel Drive,...

Some Properties may even only be relevant for less than 5% of the Cars. There are different solutions to solve this.
- The first is dump everything into one table. Of Course Normalized! (not mentioned below)

- The second solution would be creating a table with Properties that every car has. Adding a CartoDrawbar ... table to establish an m:m connection between the rare Properties and the Cars.

- The third possibility i can imagine would be creating Tables for Car Groups like SUVs, Notchback, Truck, Compact, Pickup ... to group cars with similar Properties. (my rare Properties were not the best choice to resemble this).

- Last idea is creating a table with all shared Properties and add a Char or Text Column to fill in everything special.

But which is the best Solution or the fitting Solution? Did i forget an important one? Are there differences in Speed, Filesize or ... to consider? Or some thresholds when to chose this or that solution. I have a personal favorite but i don't want to influence you and i don't have enough knowledge about the relational Databases and or Management Software to judge Speed or File-size of a Table.

Martin
  • 1
  • Check this duplicate: https://stackoverflow.com/a/3579462/2469308 – Madhur Bhaiya Oct 01 '19 at 20:46
  • And another duplicate: https://stackoverflow.com/a/190306/2469308 – Madhur Bhaiya Oct 01 '19 at 20:47
  • Hi, thanks for your post. I have to admit i didn't search for "represent inheritance". I read through both Questions but didn't get an answer. @daniel-vassallo wrote about pros and cons but just in an matter of "is the Code DRY", "how difficult is it to work with", ...
    No mention on how the different solutions behave in a Benchmark test.
    How scalable to big Data they are...     – Martin Oct 01 '19 at 21:30

1 Answers1

0

There is no "best" solution. In fact, most of your "rare" columns look more like flags -- a car has 4-wheel drive or it does not, a car has a roof-rack or it does not.

My suggestion is to put these into one table, with separate columns, of the appropriate type.

Then, if you really do have optional features, like say the number of gears in a manual transmission, you can then think about how to implement a list. Nowadays, most databases support JSON and that would be a natural choice for such elements.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Use of JSON is a great idea. Creating another Solution. Will have to try that soon.
     – Martin Oct 01 '19 at 21:33
  • You are right my examples for rare attributes or properties were poor. Lets say 4-wheel drive model. If i add a column my question is will the database reserve space for every empty/Null value? Next step would be own Table for property
    Table FourWheel (CarId int, Model char);
    But now Model is not normalized leading to a three Table solution to represent the m:m connection. Making Handling worse and creating a lot of Keys which also consume space.     – Martin Oct 01 '19 at 21:42
  • Space is reserved, typically even for `NULL` values. However, it can be quite small for tinyints or `varchar()`. – Gordon Linoff Oct 02 '19 at 00:37