Designing my MySQL Database for rating things on different days

I've been using reddit as my help recently, and they've been great but then I found this site and feel it's a much more appropriate place to ask my questions. Hopefully 开发者_如何学编程the helpfulness trend continues here. Let me start by saying I know this project is pretty intensive considering I have never done anything like it before, however that doesn't change the fact that I am doing it and I am willing to learn how.

I am designing a website which will feature the the bar and dining specials for my city. Users will be able to log on, and the home page will display the top 5 rated bar specials/ happy hours, and the top 5 dining specials/happy hours for that day(i.e. monday, tuesday, etc.). Each "special" will have an up down rating for the user to give their input on the special.

A bar page will list all of the bars and their specials for that day, and the option to upvote or downvote the special. Same thing with a dining page. From there, the person can click on the bar name to be taken to a page specifically about that bar. That page will show the specials for the entire week.

I'm in the process of designing the MySQL table now in phpmyadmin, as it seems like that's where I need to start after having written the html and css for the design. I've tried googling for a framework that I could build off of, however I didn't come up with anything relevant.

Would it be best to make separate tables under each database for each day of the week, put everything into one table, or another way entirely. This is what I made so far, let me know if I am on the right track or not. Thanks a million! !(http://dl.dropbox.com/u/16887445/Screen%20shot%202011-07-06%20at%208.01.10%20PM.png)

Day of the week is an attribute of a "specials" record, not a record unto itself. Picture this -- if you have one table per day, and you want to get all specials for one bar for a week, you'll have to make seven queries:

SELECT * FROM sunday_specials WHERE
  establishment_id = <id> and date = <sunday>;

SELECT * FROM monday_specials WHERE
  establishment_id = <id> and date = <monday>;

SELECT * FROM tuesday_specials WHERE
  establishment_id = <id> and date = <tuesday>;
...

This is (hopefully) obviously a bad design. Whay you want is to do be able to do it with one query:

SELECT * FROM specials WHERE
  establishment_id = <id> and date >= <sunday> and date <= <saturday>;

Note also that you don't even really care about storing the day of the week, all you need is the date (which you need anyway), from which you can extract day of the week. I'd create a schema something like this:

-- This holds one record for each bar/restaurant.
CREATE TABLE establishments
(
    id INT SERIAL,
    name TEXT NOT NULL,
    address TEXT,
    phone TEXT,
    uri TEXT,
    type INT NOT NULL DEFAULT 1,
    PRIMARY KEY (id)
);

-- This holds one record per day per establishment. Not storing the
-- day of the week allows you to keep all records going back in time.
-- Up/Down voting simply increments or decrements the rating field.
CREATE TABLE specials
(
    id INT SERIAL,
    establishment_id INT NOT NULL REFERENCES establishments(id),
    special_date DATE NOT NULL DEFAULT NOW()::DATE,
    name TEXT NOT NULL,
    description NOT NULL,
    rating INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

So, to display the specials for "My Bar", you do something like:

$start_date = <whenever>
$end_date = <whenever>
$id = SELECT id FROM establishments WHERE name = 'My Bar';
$specials = SELECT * FROM specials WHERE
    id = $id AND special_date >= $start_date AND special_date <= $end_date;

To display the top five rated specials for Monday:

SELECT * FROM specials WHERE special_date = <monday> ORDER BY rating DESC LIMIT 5;

If I understand what you're going for correctly, this is probably how I'd design the schema:

TABLE location // For storing details about the bars & restaurants
location_id (pkey)
location_name
location_type // For flagging if it's a restaurant, bar, other, etc.
location_address
...

TABLE specials // Holds all the specials for all days of the week
specials_id (pkey)
location_id (fkey)
day_of_week // 1-7 values
special_name
special_detail
...

TABLE votes
vote_id (pkey)
specials_id (fkey)
vote_type // Flag for up/down (1, -1)
...

Once you have the data normalised, you can use queries or views to pull the aggregated data. For example, to find all the up votes for a special, you could use a query like this:

SELECT COUNT(*) FROM votes WHERE specials_id = '1234' AND vote_type = '1'