I'm working on a project where a sports club can add members to it's club. Those members can post thread on a message board.
Now when designing i have the following question.
Lets say i have to following tables: clubs, users, threads
There is an association between the clubs and users table.
Now in the threads table i have a created_by column, is it usual to associate this with only the users table (user_id) or to both the users and the clubs table?
I'm asking this beceause i also want to display a list of started threads for this club. If i only link it to the users table i can work with join statements, but if i link it to both i could also select on the club_id.
...is it usual to associate this with only the users table (user_id) or to both the users and the clubs table?
let's say you get a situation where a user changes from one club to another
you would normally (no pun intended) just change the user's club link in the users table and you're finished
but if you have linked the threads table to both users and clubs, then you have an update anomaly -- when a user changes clubs, you would not only have to update the users table, but also the threads table, changing every thread the user is involved in
the pun i made just then was in reference to normalization -- linking to both users and clubs in the threads table breaks third normal form