1. 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?

Discussion in 'XenForo Development Discussions' started by Marcus, Feb 14, 2015.


I would prefer

  1. A multiple tables

    3 vote(s)
  2. B one table with all properties

    0 vote(s)
  3. C ...

    0 vote(s)
  1. Marcus

    Marcus Well-Known Member

    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
    table plane_color: color_id, plane_id, color
    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
    Alfa1 likes this.
  2. Brogan

    Brogan XenForo Moderator Staff Member

    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.
  3. Alfa1

    Alfa1 Well-Known Member

    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?
  4. Luke F

    Luke F Well-Known Member

    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: Feb 14, 2015
    Alfa1 likes this.
  5. Marcus

    Marcus Well-Known Member

    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.
  6. Marcus

    Marcus Well-Known Member

    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.

Share This Page