3

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?

upd:

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

HelloWorld
  • 103
  • 4
d7p4x
  • 133
  • 1
  • 6

2 Answers2

7

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.

David Spillett
  • 32,593
  • 3
  • 50
  • 92
3

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.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536