VS2008/SQL Server: Quickly set up many-to-many-tables

Question

Hi

I've recently started using Visual Studio 2008 and SQL Server Express. I'm coming from a Emacs/Django background, so I'm not used to this way of working. Is there any quick and easy way to set up a many-to-many-table between two other tables? This would be the equivalent to the table generated by a ManyToManyField in Django.

Answer 1

Yes, let's say you have two tables: SALESPERSON and SALESREGION. A single sales person can be associated with more than one region, and vice-versa. Let's say that SALESPERSON has a non-composite primary key: salesperson_id (int,pk) and SALESREGION has the same: salesregion_id (int,pk).

Create another table called SALESPERSON_SALESREGION with two foreign key columns: salesperson_id (int) and salesregion_id (int). Create a composite primary key for this table that includes both columns.

Now, adding and removing relationships is as simple as adding and removing rows in the new table.

Some ORM frameworks like Entity Spaces, and I assume the new ADO.NET Entity Framework recognize this pattern and will create many-to-many structures in the generated entity data access layer.