history table and 2nf

Users table and Group users table are history tables (they cannot be moved to archived tables cause that would mess up foreign keys related to those tables. If a group user made a post and left the group, the post should still exist etc)

Users

Username

Start Date

key (Username, Start Date)

Group Users

Group Name

Username

Start Date

Group Start Date

Primary Key (Group Name, Username, Group Start Date)

Foreign Key (Username, Start Date)

According to 2NF, any nonkey field cannot be a fact about a subset of a key. In this case, Username is part of a composite key and Start Date is a fact about Username, th开发者_运维问答erefore this table is not in 2NF.

So would the fix be to include the start date as part of the key?

---

Start Date is a fact about Username

Not it isn't. Start date cannot be determined by Username in your model because (Username, Start Date) is a key - the Username and Start Date together are needed to identify a row in the users table.

---

The PK of Group Users is arguably incorrect because combination of UserName and StartDate is required to uniquely identify a user. So, if 'John' (the first) joined on 2011-09-01 and 'John' (the second) joined on 2011-09-10, then your Group Users PK prevents them from both joining the same Group. Your Group Users PK should include both UserName and Start Date since this combination is what identifies a user uniquely.

This is why people use 'ID' columns; if you had a Users.ID column, then you'd use that simple key in the FK in Groups Users. Compound primary keys that are themselves used as foreign keys in other tables tend to be problematic (for cases such as this) and a surrogate such as an ID column then makes the DB design simpler.

You should presumably have a Group table too and Group Users.Group Name should be a FK to that table. It is a bit surprising that there is no 'unsubscribe date' in the Group Users table, nor in the Users table. Or you may prefer a 'currently subscribed' flag, but the date is also useful, in general.