1

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, therefore this table is not in 2NF.

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

firebird
  • 3,461
  • 6
  • 34
  • 45

2 Answers2

2

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • I was talking about the Group Users table. Since Start Date is not part of the Group Users key, and instead relating to part of a key (group name, username, group start date), then it can't be in 2nf? – firebird Sep 30 '11 at 20:40
  • What part of a key precisely? Username -> Startdate is obviously not a dependecy that's going to be satisfied by either table so that can't violate 2NF.(Group Name, Username, Group Start Date)-> Startdate is a *key* dependency so that's not a violation either. – nvogel Sep 30 '11 at 20:46
  • Start Date depends on part of the key, Username, in the Group Users table and not the whole key correct? It's a fact about the Username and not Group User? – firebird Sep 30 '11 at 21:01
  • 1
    "A is a fact about B" just means "B determines A". Username does not determine Start Date - there is more than one Start Date per Username. – nvogel Sep 30 '11 at 23:30
1

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278