I have two tables like products and customers and for many to many relationship i created new table like customer_product_map and referenced PK from both table.

So my question is, as each customer needs to know how many products they have, can i store something like product_count in customer_product_map table? is that good practice?

CodePudding user response：

is that good practice?

No! On the contrary, it's an absolute anti pattern.

To ensure integrity, data should be kept redundancy free. Materializing the count might result in the actual count being some different figure than the saved value. Decisions might be made upon false figures, the error might propagate even further with disastrous consequences.

There are some rare case where materializing such figures may be the last resort. But you don't seem to be even close to that.

If you want to get the count of products, fire a query that counts the rows. It will always give you the right number! For convenience, if you don't want to repeat such a query all the time, you can create a view from it.

If you want to know more about good database design, you should look up resources about so-called normalization.

CodePudding user response：

It depends, on a number of factors. Such as:

• What do you do with that value? Is it just for display?
• How critical is it that it's correct and consistent at all times? Do you have any mechanism for detecting and correcting skew? Do you need that?
• The database can count them for you -- is that too slow? If so, maybe caching that value is the right approach, but maybe adding an index is better.

Like I said, it depends on what you're trying to do.