Sql-server – Do I really need a separate table for the clients

database-designsql serversql server 2014

I have the following scenario:

  • Products table (a lot of products info, including prices)
  • Clients table (just the client id and name)
  • A CliPrice table (product price for each client, columns: id, id_prod, id_cli, price)

Would it be better to join the CliPrice table with the Clients table by adding the Client name in the CliPrice?

Is it wrong?

OBS: The same happens with a VolRegion (id, id_reg, id_prod, volume) table where I have a Region table with the region name and id only.

Best Answer

The answer depends on what use are you giving to your database. For instance, in datawarehousing it isn't rare to use some denormalization to your design in order to reduce the number of joins needed to get your data. But this is because you are almost certain that the source have data integrity, and the data is correct.

What you are proposing doesn't comply with the 2nd normal form, which states that every non key column of a table should be functionally dependent on the key columns. In your question, the client name is functionally dependent on a subset of the primary key of your table (just the client, not the product).

What are the issues with it?. Well, for starters, you are using more data than necessary, why store the whole client name once per row with every product when you can just join it to the Client table instead?. If your database is your "production" one, then you also need to worry about consistency of the data. Storing the client name in that table means that you can't be absolutely sure that you are consistent at the database level. For example, you could have a row where client id = 1, and the name is "Michael", and another row in that table where client id = 1, but the name is "Mishael".