Mysql – Is it ok to have 40+ columns in a table

database-designMySQL

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.

Best Answer

Considering that MySQL is still a relation database management system an (RDBMS if you will), you should considering the following things:

Your tables are defined by the data they contain and the relationships to other tables
Your data should be as non-redundant as possible

This last point leads to the process of normalization, which is a very important thing to do in a RDBMS.

Have a look at the Wikipedia article on Normalization to have a basic understanding of what that process entails: http://en.wikipedia.org/wiki/Database_normalization

I find that, oftentimes, it can be a good idea to take a step back from the "storage" aspect of your database. Try to look at your application, the data models used within and their relationships. Creating such an abstraction is called an ERM: Entity-Relationship Model, or an ERD: an Entity-Relationship Diagram. More info on that: http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

For your case specificially:

Apart from the fact that your not using a design that fits a relational database, please consider the fact that you are creating a very inflexible design! What if -- for example -- you were to create a new product in the future? Would you add a new column to the table? Surely you'll agree that that's pretty stupid.

Instead, it makes MUCH more sense to create something along the lines of

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
| 1         | Snake Logan | ...            |
| 2         | Mad Max     | ...            |
| ...       | ...         | ...            |
+-----------+---------- --+----------------+

+------------+---------------+---------------+
| product_id | product_desc  | product_price |
+------------+---------------+---------------+
| 1          | Silver Bullet | 14.00         |
| 2          | Hand grenade  | 7.00          |
| ...        | ...           | ...           |
+------------+---------------+---------------+

+-------------+-----------+------------+-----------------+
| purchase_id | client_id | product_id | purchase_amount |
+-------------+-----------+------------+-----------------+
| 1           | 1         | 1          | 3               |
| 2           | 1         | 2          | 1               |
| 3           | 2         | 2          | 5               |
+-------------+-----------+------------+-----------------+

I'm not saying this is the greatest design ever, but this is more elegant.

If you want to have more info concerning the purchase, I'd recommand building a view that contains the necessary info by joing the tables together. Please don't create a field like purchase_total, I think that would once again be redundant data. The data is already there (purchase_amount and product_id), you should need to do the math (purchase_amount * product_price). You can put that in the view if you want. Tables are for storing data, views are there to present said data.

Related Solutions

Sql-server – Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

I'd question why you have OrdersForProductX tables at all
It's possible the FK problem you've asked about can be designed out...

If these tables have the same structure, then you simply need a ProductType column on some OrderProduct table. Then Payment just links to that with one FK

If the table have different structures, I assume they have some common attributes. So, you can have a common OrderProduct table then specific child table per product type (see below) Again, Payment just links to the commone table with one FK

This is the "superkey/subtype pattern"

UQ1 is the "super key" used a foreign key on the subtype tables
Each subtype table has a composite PK and FK on (OrderID, ProductType)
Each subtype table has a CHECK constraint to restrict types in that table

OrderProduct

OrderID, PK, UQ1
ProductType, UQ1
CommonThing1
...

OrderProductA

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = A
ProductAThing1
...

OrderProductB

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = B
ProductBThing1
...

Design Pattern – One of Many Parent Tables

I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.

You are stuck to just two solutions because of the strict 3NF compliance.

I would design a lesser coupling schema:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  -- See (1)
  -- AlertID     int identity(1,1) not null,

  AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
  ForeignKey int not null,
  CreateUTC  datetime2(7) not null,

  -- See (2)
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)

-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys

Or, if integrity relationship shall be mandatory, I might design:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  AlertID     int identity(1,1) not null,
  AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
  CreateUTC  datetime2(7) not null,
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

CREATE TABLE AlertProduct  (AlertID..., ProductID...,  CONSTRAINT FK_AlertProduct_X_Product(ProductID)    REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews     (AlertID..., NewsID...,     CONSTRAINT FK_AlertNews_X_News(NewsID)             REFERENCES News)

Anyway...

Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...

These presented, what's the moral?

They differ subtly, and weight the same on the criterias:

performance on insertions and updatings
complexity on querying
storage space

So, choose that comfier to you.

Best Answer

Related Solutions

Sql-server – Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

Design Pattern – One of Many Parent Tables

Related Question