Implementing a 'many-to-many' database

Question

Greetings, stack*overflow*

In my database, I already have one table, 'contacts' that contains records of individual people. I also have several other tables in my database which represent "skill sets" that contain records denoting a particular skill.

1) Am I correct in plotting this as a "many-to-many" relationship? (each contact can have multiple skill sets, and each skill set can belong to multiple contacts)

2) I'm new to databases -- do I want to link the tables?

3) Is there a way to implement this in my program (C# + windows forms) such that for any given record in the 'contacts' table, either the names of all associated 'skill set' tables or all the 'skill' records associated with the 'contact' record could be retrieved?

(Database is located on SQL Server Express 2008. Data is being retrieved from the database via VisualStudio 2008's built in "Data Connection Wizard")

Answer 1

You are correct. That should be a many to many relationship. To accomplish this goal in SQL, you should have three tables:

Contact - Stores contact information
SkillSet - Stores individual skill set information
ContactSkillSet - Maps skill sets to contacts based on primary keys

As for your last question, there are multiple ways to do that. It all depends on how you are accessing your data (DataSets, LINQ to SQL, Entity Framework, etc.). If you provide more details, we can give you a more specific answer.

Answer 2

You use a linking table that contains the primary key from each of the two tables you need to link together. Put a unique index onthe combination of the two keys or make the combination the PK.