Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2005-10-06 : 11:16:46
|
| A database I am porting to SQL Server has about 150 many to many links and 40 tables. Some links are between the same pair of tables, but have different purposes, for example Appointment_CoordinatedBy_User and Appointment_AttendedBy_User. I am looking for advice on the best way to structure this.1. I have seen SQL Server databases that have one table for each link, with just the Table1ID and Table2ID columns. Northwind takes this approach with the CustomerCustomerDemo table. In my example above, I would have the following tables:AppointmentAppointment_CoordinatedBy_UserAppointment_AttendedBy_UserUserThe index in each table would be on Table1ID+Table2ID.2. I could add the LinkType column to the linking table and have only one linking table between each file pair:AppointmentAppointment_UserUserThe index in each table would be on LinkType+Table1ID+Table2ID. This would cut down the needed link tables to approx. 130.3. In the current database all n-n links are defined in one big table with text columns Table1Name, Table1ID, Table2Name, Table2ID, and a smallint LinkType (which describes the purpose of the link).The index is LinkType+Table1Name+Table1ID+Table2Name+Table2IDApproaches from 1. to 3. go from the smallest index and simplest query syntax to the largest index and most complex query syntax. I wonder about performance, reindexing issues and overhead of having a lot of tables. What would you do?TIA. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-06 : 21:59:11
|
| In 3 the linktype should define the tables. You can have another table which describes the link if you wish to give the two table names but putting the redundant info in the linking table is a bad idea.Apart from that there's not much difference.I would probably go with no 3 (with table names removed) in your stuation just to cut down the schema size. Also depends on how often thes are changed as 3 could end up with blocking or deadlocks.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Large vs. multiple linking tables