MySQL - move from flat table to first normal form

Question

Hello

I am building an application that will allow a user to record weekly activity over a 6 week period. Each week has 3 benchmarks to record against, here is an example:

Week 1

+------------+-----------+------------+-----------+
| Day        | Minutes   | Location   | Miles     | 
+------------+-----------+------------+-----------+
| Monday     |           |            |           |
+------------+-----------+------------+-----------+
| Tuesday    |           |            |           |
+------------+-----------+------------+-----------+
| Wednesday  |           |            |           |
+------------+-----------+------------+-----------+
| Thursday   |           |            |           |
+------------+-----------+------------+-----------+
| Friday     |           |            |           |
+------------+-----------+------------+-----------+
| Saturday   |           |            |           |
+------------+-----------+------------+-----------+
| Sunday     |           |            |           |
+------------+-----------+------------+-----------+

This is repeated for each week up to 6.

In my flat table I have the following:

UserID | Username | Week 1 Day 1 Minutes | Week 1 Day 1 Location | Week 1 Day 1 Miles | Week 1 Day 2 Minutes | Week 1 Day 2 Location | Week 1 Day 2 Miles ETC...

X 7 for a week and then X 6 for the 6 weeks.

I am trying to figure out where my eliminations are, and what my separate tables would be. So far I have the following:

User Table 
+------------+-----------+
| UserID     | Username  |
+------------+-----------+
|            |           |
+------------+-----------+

Activity Table
+------------+-----------+------------+-----------+------------+-----------+
| UserID     | WeekID    | Day        | Minutes   | Location   |   Miles   |
+------------+-----------+------------+-----------+------------+-----------+
|            |           |            |           |            |           |
+------------+-----------+------------+-----------+------------+-----------+

Weeks Table
+------------+-----------+------------+
| UserID     | WeekID    | Week_No    |
+------------+-----------+------------+
|            |           |            |
+------------+-----------+------------+

I think I am getting along the right lines, but the Weeks Table doesn't seem right and I am not sure what the relationships are - I don't think I need UserID in each table, and I'm not sure what the PKs should be.

Any comments on this schema, or an efficient way to achieve the first normal form given the application requirements would be much appreciated, many thanks.

EDIT:

Thanks very much for all the answers, great stuff.

I think having a Location Table would be beneficial as I could standardize locations (could provide a list to choose from) and if I need to query based on location, I'll have consistent location names.

Revised the schema to this:

User Table - UserID PK
+------------+-----------+
| UserID     | Username  |
+------------+-----------+
|            |           |
+------------+-----------+

Activity Table - ActivityID PK
+------------+-----------+------------+-----------+------------+-------------+-----------+
| ActivityID | UserID    | Week_No    | Day       | Minutes    | LocationID  |   Miles   |
+------------+-----------+------------+-----------+------------+-------------+-----------+
|            |           |            |           |            |             |           |
+------------+-----------+------------+-----------+------------+-------------+-----------+

Location Table - LocationID PK
+------------+---------------+
| LocationID | Location_Name |
+------------+---------------+
|            |               |
+------------+---------------+

2nd EDIT:

I now have a question on 2NF and 3NF on this topic:

http://stackoverflow.com/questions/3462534/mysql-moving-from-1st-normal-form-to-2nd-and-3rd-normal-forms

Answer 1

Add a Location table and change Location to LocationID (PK). The Weeks table does not need UserID in it. You can find what weeks a user has by querying the Activity table.

I only see the need for a Week table if Week_No changes by user, which doesn't seem to make too much sense. Otherwise, you can just replace WeekID with WeekNo in the Activity table, and delete the Weeks table.

Answer 2

Hi,

In the weeks table your primary key should be WeekID. I'm not sure you would need a week table though as you don't seem to be storing anything in it apart from the week that the activity took place, which could actually be in the activity table. So I would get rid of it, add Week_No to the Activity table, and have an ActivityID in the Activity table as primary, and UserID as Foriegn Key.

Don't want to tell you too much, just enough to get you on your way as you seem to want to normalise this fully own your own.

Answer 3

PKs: User tbl: UserID Weeks tbl: WeekId Activity tbl: (UserID,WeekID)

And you don't need a UserId in Weeks Tbl

Answer 4

i don't think you need a week table.

activity table with userid, weekno, day(enum), minutes, location, miles, is enough for what you mentioned...