I have an object, that is described by 40 properties, is it good a idea to create a table with 40 columns? I think that I cannot break these properties into another table (such as table_properties_1 and other).

What do you think, professionals?


Offer table will be contain more fields... simple diagram

  • 1
    Tens of columns isn't a big deal, as long as you're not doing some denormalized nonsense. Just follow good practice to make sure you're not SELECTing more data than you need, don't store in columns what should be rows...should be just fine – Ben Brocka Jul 30 '12 at 15:07
  • 2
    This question was migrated from code-review because we kind of specialize in schema-design questions, amongst others. So in order to help, we're going to need an actual narrative on your schema, and preferably some sort of ER diagram that shows why you need 40 columns. If need be, some example data and surrogate keys would certainly define for us if there's any chance of making this smaller, but I can safely say that there isn't always a problem with 40 fields, it all just depends. – jcolebrand Jul 30 '12 at 17:25

I discussed this before back on Jul 20, 2011

See my answer to Too many columns in MySQL

You should really think about normalizing your table once you are using more than 10 columns

If you feel you must have 40 columns, then I would suggest migrating the following columns types

  • TEXT
  • BLOB
  • long VARCHARs

into one table and keep PRIMARY KEYs for needed info in a main table. You would have to perform JOINs when data from both tables are needed. I suggest this because if a 40-column table becomes the source of a temp table that will involve all the columns, you will produce very bloated, unindexed temp tables during join and sort phases of queries and that would slow the query down.

UPDATE 2012-07-30 15:51 EDT

Looking at the Offer Table, I can easily see two tables you can strip out.

  • Move autobrand and automodel into an automobile table
  • Move credit and credittype into a credit table

This could give you flexibility to query for different automobiles and credit scenarios.

  • 1
    I'm all for the 'stop and think' approach, but doubting yourself at 10 columns seems awfully low. – rfusca Jul 30 '12 at 17:49
  • @rfusca I have seen horror shows with less that 10 columns from developers of my company's clients. I know 10 columns is a little ultraconservative. In my past post I did balk at 20 columns. That's why I say stop and think. Once developers stop and think they can prepare to code and live with what they designed. – RolandoMySQLDBA Jul 30 '12 at 17:53
  • If you've seen it at less than 10 ... then really, the goal is to 'stop and think' ALWAYS, not just in relation to number of columns past 10. I'm just saying that planning and thought shouldn't be a function of the number of columns. There are perfectly good reasons for 12 columns in a table, or 8, or 40 - and all of them should have been reasoned out beforehand. – rfusca Jul 30 '12 at 17:57
  • @rfusca I agree with your last comment. I have not been a developer in the last 8 years. I am just an operational DBA now, but I still recommend DB changes to Web Hosting Clients out of mercy. Forgive me for sounding too pessimistic. – RolandoMySQLDBA Jul 30 '12 at 18:08
  • 1
    @rfusca some of your thoughts from your comments should be written up as an answer. – RolandoMySQLDBA Jul 30 '12 at 18:09

There is nothing wrong with tens of columns in a table. Depending on the property types it can be more efficient in terms of space than a separate properties table, and it is almost always faster to query if you want several of the properties at once in one row of a resultset, i.e. it you want to return:

Thing  Prop1  Prop2  Prop3
-----  -----  -----  -----
T1     T1P1   T1P2   T1P3
T2     T2P1   T2P2   T2P3

instead of:

Thing  Property  Value
-----  --------  -----
T1     P1        T1P1
T1     P2        T1P2

because you won't have to have many joins (or, worse, subqueries) to the properties table to transpose the data. Of course if you regularly need the data in this transposed format then this recommendation is reversed as you'll need to transpose the other way (with several unions) otherwise. Even if your DB has efficient transposition functions, make sure the data is in the arrangement you are most likely to need it in most of the time.

Most DBs allow many columns per row and are often not much less efficient with wide tables than they are with thin ones (unless you "select *" all the time and/or have bad index choices).

The massive caveat to the above is that you should be careful that you are not creating a nightmare for yourself by way of denormalisation. We could only give advice on that matter if we knew what the column contents were expected to be.

  • This is an interesting perspective. I don't know why it should be downvoted. I can see this answer as being useful with reference to the datatypes and reducing joins. My answer did not go into that kind of depth. +1 !!! – RolandoMySQLDBA Jul 30 '12 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.