Hello I am in a problem of designing a web-app database schema.
Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like some shops give deals from 4 to 5 pm, some 8 to 10 pm, 4 to 10 pm.
Apart from that I have few categories on which a shop will give the deals like beer, wine, foods etc. For example, suppose categories are 1, 2, 3, 4, & 5.
Shop A gives deals on 1 and 2 on Sunday from 4 to 6 pm
Shop A gives deals on 2 and 3 on Mon from 7 to 8 pm
Shop A gives deals on 1 and 5 on Wednesday from 7 to 8 pm
Shop B gives deals on 1 and 2 on Sunday from 4 to 6 pm
Shop B gives deals on 2 and 5 on mon from 7 to 8 pm
Shop B gives deals on 1 and 4 on sturday from 7 to 8 pm
Shop C gives deals on 1 and 3 on Sunday from 5 to 6 pm
Shop C gives deals on 3 and 5 on wed from 5 to 8 pm
Shop C gives deals on 2 and 4 on fri from 4 to 8 pm
There is a possibility that any shop can provide the same type of deals every day or maybe 3 or 4 or 5 days of the week and the rest of the days are different like
Shop D gives deals on 1 and 3 on Sunday, Mon, Tue and so on from 5 to 6 pm.
Shop E gives deals on 1 and 5 on Sunday, Mon, Tue and Fri from 5 to 6 pm.
Shop E gives deals on 2 and 4 on Wed and Thu from 4 to 6 pm.
In this case there will be redundancy, duplicity of the same type of data that I also want to stop.
Now I am totally confused how should I design the database for this problem?
Help will be appreciated.
Thanks
Best Answer
From what I understand of your problem your "duplicity" is fine. If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate.
Another example, I am answering this question now, but I expect someone else is going to hit the submit button to a different question at exactly the same time, so in their "answer" table (or whatever) there will be two rows with "duplicate" data on it (the time of answer would be the same), is this a problem..... no because the data is unrelated, its simply a record of what happened.
The only time I would suggest normalising this further is if you had to attach extra information to the time which would be the same (so if there was information that would always be the same whenever any shop does any deal that starts at 6pm on a Sunday). A counter argument to this could be do you have shops in different time zones? If yes then 6pm could mean 6pm local time, but not at the same time, in this case what possible extra information could there be which would always be the same in every country, time-zone, shop and deal.