mysql denormalization of multiple entities in one table?

Question

I am building an eCommerce multichannel listing tool for ebay/amazon/sears/rakuten ... and more

each entity has its own properties. for example eBay has ebayItemId/Title/price while amazon has something like asinNumber/Title/LowestPrice

My question is should I have each one in its own table. or should I mix the entities together in one table, The column header can store different data based on the marketplace, A lot of columns might have null values.

you think this is a good approach or is it better to normalize them to multiple entities?

score 1 · Answer 1 · edited May 23 '17 at 12:01

1

The way to evaluate what type of denormalization you should do is to start with the queries you need to answer, then organize the data to help the queries.

You can't find the best table structure without taking the queries into consideration.

For example solutions for your use case, see my answer to https://stackoverflow.com/a/695860/20860

edited May 23 '17 at 12:01

Community

1
1

answered Dec 11 '16 at 08:10

Bill Karwin

538,548
86
673
828

ok so I have a parent entity. which is basically an item with pictures and some basic properties. the children would be these marketplaces listings. I guess the queries are write intensive. also I think it will be much easier to pull the children from one table and loop through them and show the specific view based on the marketplace flag. – Sherif Badawi Dec 11 '16 at 08:18

score 0 · Answer 2 · answered Jan 14 '17 at 21:22

It's best to have a fully normalised schema. Everything is simpler and consistent.

You only denormalise for "performance", which is a different need than the benefits that normalisation gives. So it's best to denormalise via a view, or a special table for that purpose, or another NoSQL database etc.

Make your correct normalised database the source of truth.

Populate/derive your denormalise data from the source of truth and use it for high speed read only operations. How you wire up the two is an implementation detail - there are many options depending on exactly how you implement the design.

mysql denormalization of multiple entities in one table?

2 Answers2