September 14, 2009 at 9:36 am
I've a fair number of tables in my various databases, which are there for the sole purpose of accommodating many-to-many associations. The tables are very simple - they contain only two columns, namely, the ID number of the primary key(s) in one table and the ID number of the primary key(s) in the other table. EG:
PK_1 PK_2
1 1
1 2
1 3
1 4
1 5
2 1
2 4
2 6
3 2
3 8
3 10
3 12
Technically speaking, one column is the "owner" of the other column, such as, for example, one column might be a Company ID while the other is a Contact ID. However, I've never actually designed a table such that the Primary Key of the table was something other than an Identity field.
So, my question is simple: Which of the following cases is ideal?
1. Leave the table as-is. Both columns are Foreign Keys, and in some cases, two indexes exist, with each index having one column being the first and the other column being the second.
2. Modify the table, and make PK_1 the Primary Key of the table. Also, in some cases, an index would need to exist for the other column. A question though, in that case - can a Primary Key also be a Foreign Key?
3. Modify the table, and add a new column, which is an identity column, and make that field the Primary Key. As in case 1, this would also entail situations where we would need two indexes.
September 14, 2009 at 10:01 am
I have seen this in lot of databases. Some having just two columns for association and some with a primary key (identity values) . I would go either for the first one or the last one. But why did this thought come for modifiying this in the first place?
---------------------------------------------------------------------------------
September 14, 2009 at 10:07 am
I've set mine up where the primary key consists of both columns (uniqueness) and then make one nonclustered key with the columns reversed (It is automatically a covering index).
So I'd go with number 1. What purpose would an identity column serve except to take up space? The nonclustered index with reversed columns will never use it because it will never need to refer to the primary table. If you ever have to delete a row, your query will be based on the values of your original two columns, not on the ID.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2009 at 10:07 am
No reason in particular, I mean if you're wondering whether there's been performance problems or something to that extent, then no, there hasn't been. It was more just a random discussion I had with one of the other DBA's at my company, and we couldn't really come to a consensus. My belief was that adding a Primary Key Identity column would simply add more overhead to a simple setup, while he was of the mindset that having the column would make all queries faster and more performant.
September 14, 2009 at 10:12 am
In the sense that every table should have a primary key, yes, a mapping table SHOULD have a primary key.
Whether it should be a separate column or can be implemented as a composite key over the two FK's is a matter of your design.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 14, 2009 at 10:15 am
having the column would make all queries faster and more performant
Ask him how it is supposed to do that? Fewer rows will fit on a page, so arguably things will run slower.
I'm not being sarcastic, but please ask for an example of a query that would actually put that new identity column to use.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply