October 6, 2005 at 9:32 am
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:
Appointment
Appointment_CoordinatedBy_User
Appointment_AttendedBy_User
User
The 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:
Appointment
Appointment_User
User
The 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+Table2ID
Approaches 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.
October 7, 2005 at 2:50 am
1. I would go with
Appointment
Appointment_User
User
with the middle table 'having a 'purpose' code added. This would allow you to define other interactions between 'appointment' + 'user' without having to define/code another table. In effect you futureproof the application.
3. As you describe it, your n-n links sound dubious. I would prefer to define real tables with proper linkages between them. It's not an issue with the number of tables, etc...SQL can hold thousands....it's more an issue with modelling the data and its relationships properly. Good database design will simplify the programming. Fudge's like that described will melt when under pressure...just like the physical substance.
October 7, 2005 at 8:18 am
Thank you for your input. The 'application' is actually a tool that allows users (admins) to redefine the schema. Tables, fields, indicies, and constraints are generated/dropped dynamically. I agree, my example was bad - Coordinated by User is a 1-n link. Perhaps a better example is Opportunity_Involved_Company (tracks multiple contractors who may help win the opportunity) and Opportunity_Competitor_Company (vendors that sell competitive products).
October 9, 2005 at 6:19 pm
Don't generalise relationships like this.
What will happen to your relationship if, for example, you later want to add attributes like "Competitor Lead Consultant", or "Opportunity Partnership Contribution", where those attributes are only relevant to one of the relationships?
October 10, 2005 at 12:59 pm
In some cases a table will be added between existing tables, for example to track competitors' products or rate competitors' competitive influence. In other cases more links may be added.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply