MySQL - moving from 1st Normal Form to 2nd and 3rd Normal Forms

Question

Hello

This question directly relates to a previous topic "MySQL - move from flat table to first normal form" (http://bit.ly/9pvS0Y) - and as I am now asking a question about moving to second and third normal forms, I figured it best to start a new topic.

Below is my first normal form schema which I am pretty sure is solid enough for my purposes, but please correct me if I am wrong.

I would like to know how to move this through to the second and third forms, any pointers as to how my tables would be affected by 2NF and 3NF rules would be really useful, thanks.

Relationships

-Activity and Location relationship = one to many - one activity can have one location, a location can have many activities (LocationID as FK in Activity)

-Activity and Week relationship = one to many - one activity can have one week, a week can have many activities (WeekID as FK in Activity)

-User and Activity = many to many - one user can have many activities, one activity can have many users

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


Activity Table - ActivityID PK / WeekID FK / LocationID FK
+------------+-----------+------------+-----------+------------+-------------+-----------+
| ActivityID | UserID    | WeekID     | Day       | Minutes    | LocationID  |   Miles   |
+------------+-----------+------------+-----------+------------+-------------+-----------+
|            |           |            |           |            |             |           | 
+------------+-----------+------------+-----------+------------+-------------+-----------+


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


Weeks Table - Week ID PK
+------------+-----------+
|WeekID      | Week_No   |             
+------------+-----------+
|            |           |
+------------+-----------+


User_Activity Table 
+------------+---------------+
| UserID     | ActivityID    |
+------------+---------------+
|            |               |
+------------+---------------+

Answer 1

Not sure of the purpose you have for table User_Activity, since you already have both columns defined in you Activity Table. Otherwise--this design already goes to 3rd normal form.

Answer 2

Unless this is an academic exercise I suggest you don't "move through" the normal forms. The formal name for that process is Normalization by Decomposition. It's not very practical in most cases however and is generally completely unnecessary.

In practice it makes more sense to start with a schema that is already hypothetically normalized (typically aim for 5NF or BCNF rather than 3NF) and then validate it. The process of designing a schema already in the desired NF is called Normalization by Synthesis and it is closer to the way most practitioners work than the decomposition method. There are several precise techiques for achieving Normalization by Sythesis but by far the commonest method is just a combination of good analysis, experience and common sense.

Answer 3

As russjudge says, I can't see the purpose of the User_Activity table.

In normalisation, it's generally best practice to use natural keys wherever possible. As such, I can't see the point of the Weeks table, either - why not just use Week_No? (A possible exception here might be if you wanted to set up "weeks" with differing numbers of days in them - in which case, you might also want to include start and end dates on the Weeks table. As it stands, though, I don't see the point.)

In fact, I would go further, and replace week and day on the Activity table with a single date field - this should actually be part of 1NF (removing derived data). Most versions of SQL (including MySQL) can readily convert a date field into a day or week, as required.

I would also be tempted to remove the ActivityID field, and instead use a combination of the UserID, Date and LocationID as a compound primary key on the Activity table. However, there may be a requirement to record multiple rows for the same user, date and location - in which case, the ActivityID field should remain as the table's primary key.