How i should design database ER-Diagram for this problem.

Question

Hello! i have some specific question to solve but i can not think.

I have 5-6 statements that I need to store in my database. This system like a news feeds.

1. Statement 1 : A installed this website.
2. Statement 2 : A added store R in provinceX
3. Statement 3 : B reviewed store R
4. Statement 4 : A edited store R
5. Statement 5 : A added product P in product_category1
6. Statement 6 : B review product P

Note that bold is dynamic data such as A, B is some preson's name, store R is store'name that person add.

In my idea, i have

• person_table(id, name, age, ...)
• store_table(sid, store_name, province_id, ...)
• product_table(pid, product_name, ...)

and how about feed_table??

How I design database to store this data. and How i query this data to view.

thank you

Answer 1

There are two approaches to this kind of problem:

1. You design your tables in such a way that you have no repetition of information. Basically the feed you're interested in can be constructed from the existing tables in a performant manner; or

2. You repeat certain data to make your feed easier to implement.

Personally I would probably go for (2) and have a new table:

• Feed: id, person_id, store_id, action_id, province_id, product_category_id

with the last two fields being optional, based on the action (review, edit, add, etc).

Purists will argue that repeated data is bad (certainly normal-form seeks to factor it out) but in the real world database schemas do this all the time for performance reasons.

Think about it this way: what do you spend most of your time doing in your application?

1. Viewing the feed (reading); or
2. Doing actions (writing).

If it's (1), which I suspect it is, then a feed table makes sense. You typically want to optimize for reads not writes as writes occur far less often.