Q:

Database design for the web-app

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 gives deal on 4 to 5 pm some 8 to 10 pm,4 to 10 pm.
Apart from that i have few categories on which 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 same type of deals on everyday or may be 3 or 4 or 5 days of the week and rest day 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, fri from 5 to 6 pm. Shop e gives deals on 2 and 4 on wed,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.
I am using postgress
Help will be appreciated
Thanks

A:

First thing that comes to mind:
Three static tables joined by a "records" table:
Shops:
| id | Name |
~~~~~~~~~~~~~
| 1  | A    |
-------------
| 2  | B    |
-------------
| 3  | C    |
-------------
...and so on...

Timeframes (day / time-frame combos):
| id | Day | StartTime | EndTime|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 1  | Mon | 2:00pm    | 3:00pm |
---------------------------------
| 2  | Mon | 7:00pm    | 8:00pm |
---------------------------------
| 3  | Sun | 2:00pm    | 3:00pm |
---------------------------------
... and so on...

Categories:
| id | Name     |
~~~~~~~~~~~~~~~~~
| 1  | Alcohol  |
-----------------
| 2  | Clothing |
-----------------
| 3  | Groceries|
-----------------
... and so on...

Then comes your Records table:
| id | Shops_ids | Timeframe_ids | Category_ids |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Inside this table, you store arrays (possible in PostreSLQ). This keeps you from having so many almost-duplicate rows.
For example: "Store A and B are both offering deals on categories 2 and 3 on Monday and Sunday from 2pm to 3pm" could be stored as:
| id | Shops_ids | Timeframe_ids | Category_ids |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 1  | [1,2]     | [2,3]         | [1,3]        |
-------------------------------------------------

However, the point of a relational database is that the data on the back end can be complex and computers are really good at finding data quickly.
So, if I were you, I would just create the Records table with individual values, you could index the columns you are most likely to search, which would be really hard to do with array values. The Records table will be large and messy, but much easier to query.

Example
1.) Shop A gives a deal on categories 1 and 3 from 4pm to 5pm on Monday through Friday
2.) Shop B gives a deal on categories 1 and 3 from 1pm to 6pm on Thursday and Saturday
Given: Shops and Categories are already in their respective tables.
Let's adjust our definition of 'Day' to accommodate the following:
M = Monday
T = Tuesday
W = Wednesday
H = Thursday
A = Saturday
U = Sunday
(This string could be generated from a series of checkboxes on a form to keep formatting)
TimeFrames
| id | Day  | StartTime | EndTime|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 1  | M    | 2:00pm    | 3:00pm |
----------------------------------
| 2  | M    | 7:00pm    | 8:00pm |
----------------------------------
| 3  | FAU  | 2:00pm    | 3:00pm | *(Friday, Sat, Sunday)
----------------------------------
| 4  | MTWHF| 4:00pm    | 5:00pm | *Shop A's sale.
----------------------------------
| 5  | HA   | 1:00pm    | 6:00pm | *Shop B's sale.
----------------------------------

Records
| id | Shops_ids | Timeframe_ids | Category_ids |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 1  | [1,2]     | [2,3]         | [1,3]        |
-------------------------------------------------
| 2  | 1         | 4             | [1,3]        | *Shop A's sale
-------------------------------------------------
| 3  | 2         | 5             | [1,3]        | *Shop B's sale
-------------------------------------------------

Hope that helps.

