What's the better table structure approach for storing properties?

[Marcus]

Storing properties for planes, what's the better and more convenient approach?

table plane: plane_id, name
1, airbus
3, cessna

A) I could create a table for each property ...

table plane_speed: speed_id, plane_id, speed
1,1,900
2,3,450

table plane_color: color_id, plane_id, color
1,3,white

B) ... or store all properties in one table:

table plane_description: desc_id, plane_id, description, property
1, 1, speed, 900
2, 3, speed, 450
3, 3, color, white

 

[Paul B]

It largely depends on the queries you intend to run.
There's no point having the data spread across multiple tables if all you're going to do is joins.

It's likely that the schema will change as you start to write the code, once you realise the optimum configuration.

 

[Alpha1]

What kind of data do you intend to pull? Things like averages, min-max, combinations of properties, conditions, or other calculations. Or just a simple storage of singular data?

 

[Luke F]

B is more of a metadata/nosql approach. You really shouldn't use it if at all possible, except for storing arbitrary objects that you have to read or query against at the DB level

If you know the fields in advance (even if some will be left out), then A is pretty much always the better approach in a relational database

If you had a good reason to be considering B (not really seeing any in your simplified example), it might be worth considering using something like mongodb instead of mysql

 

[Marcus]

Hi Luke, thanks for your answer. Why might A be better than B, I haven't figured it out. Is it because of performance?

B is obviously easier to implement and you do not have to add a Datawriter and Model each time a new property is added.

 

[Marcus]

I gave it some thoughts and remembered about database normalization from my classes.

I also so much do not agree with the way xenforo handles user profile fields, they are mostly useless for me in their current non normalized status. So if I want to really use that data later I would have to normalize it anyway.