Tweeted twitter.com/#!/StackDBAs/status/539257334200279040
2 edited title
| link

DB Design: Is it ok to have 40+ columns in a MySQL DBtable

1
source | link

DB Design: Is it ok to have 40+ columns in a MySQL DB

I am currently re-designing some of DB's for a project. There is one DB in particular that is giving me a little of a headache. The idea is that for every client_id and date (client_id + date would be the Pri. Key) each column would contain the usage for each product that we offer (around 12 products, and for each one Cost, Margin, Total needs to be stored). I want to do domething like this:

+-----------+------------+--------------+-------------+-------------+------+--------------+
| client_id |    date    | prod_1_Total | prod_1_Cost | prod_1_Paid | .... | prod_14_Paid |
+-----------+------------+--------------+-------------+-------------+------+--------------+
| aa1       | 2014-01-01 | 245          | 54.97       | 50.58       | ...  | 431.23       |
| ...       | ...        | ...          | ...         | ...         | ...  | ...          |
+-----------+------------+--------------+-------------+-------------+------+--------------+

We have to note that for a record to exist that client_id has to have had some activity in the given date, in other words, given a client_id and a date, the rest of the columns can't be all NULL, otherwise that record won't be on the table.

It seems to me a bad practice to merge all this products in one table like above, specially, when for a given client_id and date many of them will be NULL.

But the alternative that occurs to me, creating separate tables for each of the products, presents the problem that to calculate for example total_paid for all producst for a client_id across some dates, we have to do 14 left joins, moreover if we use lets say product_1 in the query as the main query and we left join against it the result would be incorrect since most-likely the for some days the client used product_x and not product_1 (Full-outer joins are not supported in MySQL)

What design strategy would you recommend?

Thanks for your suggestions.