0

Suppose I have 10 shops and every shop gives deals on every different day--Sunday, Monday, and so on--and for a limited time period some shops give a deal on 4 to 5 pm some 8 to 10 pm,4 to 10 pm.

I have a few categories for which a shop will give deals like beer, wine, foods, etc.

For example: Suppose categories are 1 through 5.

Shop A gives deals on 1 and 2 on Sun 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 Wed from 7 to 8 pm
Shop B gives deals on 1 and 2 on Sun 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 Sat from 7 to 8 pm
Shop C gives deals on 1 and 3 on Sun 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 on other days differently.

Shop D gives deals on 1 and 3 on Sun, Mon, Tue and so on from 5 to 6 pm
Shop e gives deals on 1 and 5 on Sun, 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.

How should I design the database?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Sunil Kumar
  • 1,349
  • 3
  • 14
  • 25

1 Answers1

1

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.

Community
  • 1
  • 1
Chiperific
  • 4,428
  • 3
  • 21
  • 41
  • Hello Chiperific Thanks for the help i have a little doubt about the Time-frames table as time value is variable that depends on shop on what time they give deals .. so do you think i should use this table. – Sunil Kumar Jun 17 '14 at 05:00
  • This table will also have many rows that are very similar, but if there is one unique field, the row is unique. Again, databases are designed to sort through these things quickly, so even if you have 20 rows for Monday with slightly different time frames, it's not going to cause noticeable slow down. – Chiperific Jun 17 '14 at 05:06
  • can i have your google hangout id or skype id – Sunil Kumar Jun 17 '14 at 05:42
  • 2
    @SunilKumar & Chiperific The arrays do *not* prevent redundancy. Eg you still have to prevent two rows that are the same except one has stores [1 2] and another [2 3]. SunilKumar: Use *simple* statement/tables like Shop S gives deals on category C on weekday D during hour H. If stores have similar info invent the "sale event" eg sale E involves category C; sale E includes weekday D hour H; shop S holds sale E. Then Sale E is on in schedule T; schedule T is on in hours HB thorough HE and not before or after & on days in set DS and no others. Etc. Complex query and constraint expressions. – philipxy Jun 17 '14 at 05:43
  • SO is the right forum for this, just keep posting your questions here. You can also edit your own question if you think of other things. – Chiperific Jun 17 '14 at 05:43
  • "can i have your google hangout id or skype id " - so, chip, feel like working for sunil for free? NO, of course you don't. lol! – Mitch Wheat Jun 17 '14 at 05:44
  • @Chiperific Ohk thanks Chiperific i will look forward on SO The Problem is that time is variable like i give deals on 4 to 5 on and you give the deal on 1 to 6 so making combo for the times is i think not possible cause there will be more number of permutation and combination for making time combo in Timeframes table Am i right or i am missing something – Sunil Kumar Jun 17 '14 at 06:06
  • 1
    @SunilKumar check out my example and see if this meets your criteria. I think it's a good idea to think about what philipxy said too, you may be able to further reduce redundancy by adding more models. However, you'll need to balance your simplicity: lots of tables can get just as frustrating as lots of repetitive data in fewer tables. – Chiperific Jun 17 '14 at 06:35
  • @ Chiperific Thanks for the much needed help Sir thank u so much – Sunil Kumar Jun 17 '14 at 06:46
  • @SunilKumar happy to help. Please mark my answer as correct unless you'd like to wait for a few more ideas. If you don't think I solved your problem, but did provide help, I'd appreciate an up-vote instead. – Chiperific Jun 17 '14 at 06:59