Question :
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.
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”.
Well, a good answer could be: It depends.
The ERP we use establish a flow layout to set a product price similar to this:
1- Has this product an specific price list?
2- Has the product’s family an specific price list?
3- Has this customer an specific price for this product?
4- Has this product any promotion this month (period of time)?
You should decide which bussines rules must be applied in your case.
How is client ID and name sufficient? Don’t you have address, phone, …?
Client ID is required to support additional data (today or in the future).
CliPrice does not need an ID.