Hi everyone, I'm trying to sort out my database before developing my interface any further and have run into a problem. Many to many relationships. I understand the theory but am falling short on putting it to practice for SQL tables.
The database is for a university.
I have 3 tables: a modules table and a users table.
[moduleID] [moduleName] [Rep_ID]
[User_ID] [First_Name] [Surname] [Email_Address] [Role]
Role can be 'Lecturer', 'Rep', or 'Committee_Member' and this is where my trouble comes in.
A lecturer can have multiple modules and a module can have multiple lecturers.
How do I set up tables to implement this many-to-many relationship based on a particular role? My development has come to a complete halt until I solve this.
Please note: I am using Visual Studio 2010 to interact with and manipulate my tables because of an unspecified error with SSMS.
Any quick help with this would be greatly appreciated. Thanks!
Also, as a side question in case anyone is feeling extra helpful: the [Rep_ID] in modules table. Could I simply use the User_ID as a foreign key?