locked
Multiple Tables VS Multiples Columns RRS feed

  • Question

  • Good morning,

    I am actually working with a DB Administrator who made a particular structure. We had to make sure that the users can creates fields (creates or remove) easily from a custom UI.

    To do that, my colleague actually designed a structure that creates 1 table per "field".

    You then have a master table which has entries matching all the existing columns available:

    For example:

    <cars, color>

    <cars, size>

    <cars, doors>

    and the tables for each "field" are named  "cars_doors" for example

    Each entry of this table is of the form <BMW, 3>.

     

    I was thinking that this kind of structure would be particularly inefficient when you have to do a lot of queries, but I wanted your opinion.

    Is it really more costly to query a lot of table instead of a lot of columns in the same table?

     

    Thanks,

     

    Jeremie

     

    Monday, April 26, 2010 3:40 AM

Answers

  • Yes, if you would need to join multiple tables to get information about something (say, a car), then obviously it would be much more costly to JOIN many tables as opposed to take all information from one table describing an entity.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by JSmaga Monday, April 26, 2010 10:05 AM
    Monday, April 26, 2010 4:00 AM
    Answerer
  • Looks like you DBA wan't to go for a key-value pair kind of a relationship which could be facilitated in one table e.g.

    CAR (Name, Value)

    Values could be

    Color, Blue

    Size, 8 seater

    Doors, 3

    Name, BMW Z3

    There are some advantages and disavantages

    Adv

    1. Any schema changes won't break the system as any new key value would go as a row instead of new columns

    Dis

    1. Relatively slower querying

    2. Some data redundancy

    There should be few more of both :)



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JSmaga Monday, April 26, 2010 10:05 AM
    Monday, April 26, 2010 5:38 AM

All replies

  • Yes, if you would need to join multiple tables to get information about something (say, a car), then obviously it would be much more costly to JOIN many tables as opposed to take all information from one table describing an entity.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by JSmaga Monday, April 26, 2010 10:05 AM
    Monday, April 26, 2010 4:00 AM
    Answerer
  • That's what I thought.

     

    So we agree, that for inserts it's even worse right?

     

    I mean, I have to use this structure to record data every 10 seconds.

     

    So I have about 30 lines to insert of 5 column each, which makes it, with this structure, 150 different queries. I cannot even use multiple inserts...

     

    It's not reasonable do you aggree?

     

    Thanks,

    Monday, April 26, 2010 4:58 AM
  • Looks like you DBA wan't to go for a key-value pair kind of a relationship which could be facilitated in one table e.g.

    CAR (Name, Value)

    Values could be

    Color, Blue

    Size, 8 seater

    Doors, 3

    Name, BMW Z3

    There are some advantages and disavantages

    Adv

    1. Any schema changes won't break the system as any new key value would go as a row instead of new columns

    Dis

    1. Relatively slower querying

    2. Some data redundancy

    There should be few more of both :)



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by JSmaga Monday, April 26, 2010 10:05 AM
    Monday, April 26, 2010 5:38 AM
  • Agreed, instead of that, I have 1000 tables in the DB.

    Okay guys thanks for your opinion and your help.

     

    Monday, April 26, 2010 10:05 AM
  • "Is it really more costly to query a lot of table instead of a lot of columns in the same table?"

    That depends. If each time, all the data is pulled, the single TABLE is better as it has one simple table-scan to do. If each attribute is pulled separately, the separate TABLEs will be faster, as there is much less reading to do. This is very close to having a separate INDEX for each COLUMN.

    Regardless, i wouldn't design for speed at this point; there likely isn't an issue. Instead, the design should match what the entities and attributes are, and that points to one TABLE.

    Allowing the users to add their own fields is generally not considered good design. Nonetheless, the normal approach is an EAV, Entity-Attribute-Value TABLE, which SQLEnthusiast pointed out. This is much easier to manage, and has less issues then adding new TABLEs all the time. There can be performance issues, but they can be addressed as they come up--if they come up.

    Car_Attribute(Attribute)
    Car_Attribute_Value(Attribute REFERENCES Car_Attribute, Value)

    This way the users can add records instead of fields.

    Monday, April 26, 2010 12:46 PM
    Answerer