• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

I would prefer

  • A multiple tables

    Votes: 3 100.0%
  • B one table with all properties

    Votes: 0 0.0%
  • C ...

    Votes: 0 0.0%

  • Total voters
    3

Marcus

Well-known member
#1
Storing properties for planes, what's the better and more convenient approach?
Code:
table plane: plane_id, name
1, airbus
3, cessna

A) I could create a table for each property ...
Code:
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:
Code:
table plane_description: desc_id, plane_id, description, property
1, 1, speed, 900
2, 3, speed, 450
3, 3, color, white
 

Brogan

XenForo moderator
Staff member
#2
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.
 

Alfa1

Well-known member
#3
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

Well-known member
#4
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
 
Last edited:

Marcus

Well-known member
#5
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

Well-known member
#6
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.