| Author |
Topic |
|
tribune
Posting Yak Master
105 Posts |
 Posted - 2004-02-26 : 12:17:15
|
| Say we've got a coupons table, and a products table:----------------------------|Coupon ID | Name | Amount |-------------------------------------------------------------|Product | Name | Size | Weight |---------------------------------Since one product can have many coupons, and many coupons can be applied to one or more product, there's a many to many relationship. So typically you use a many to many relationship table...(PK) (PK)--------------------| Product | Coupon |--------------------...with two non-clustered indexes on Product and Coupon right?My question: is there any benefit to designing the table so you've got a identity PK like so:(PK) ------------------------------------ | AssociationID | Product | Coupon |------------------------------------With a clustered index on AssociationID? Relationships in this table may be created and broken at any time as well. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-26 : 12:38:58
|
| Nope.There are many names for that type of table - I prefer conjoint.==========================================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. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 12:50:10
|
| Do not add AssociationID to the relationships table. Put the PK over product and coupon.Tara |
|
|
|
nfsoft
Starting Member
36 Posts |
Posted - 2004-02-26 : 12:52:32
|
| May be, or may be not!...I had a big and long fight about this... there is no right answer about performance (false argument).Keep in mind that if this table is static (as is... no administrative changes on it... the database will not grow...) keep it in (PK,PK) else use a surrogate key (PK | FK | FK ), like this you may in the future add a column about a state a build a history on it.Nuno Ferreira |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 13:00:29
|
| For a conjoint table though, do not add the identity column.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-26 : 13:23:58
|
| nfsoft I'm having trouble understanding your reply. This table will have rows inserted frequently, and deleted every once in a while (perhaps a ratio of 10:1 inserted:deleted). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 13:25:34
|
| Nuno's response is just about whether to put an identity column on a table or not to. It's an old debate. But I'm pretty sure that this debate doesn't exist for a conjoint table.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-26 : 13:27:26
|
| So lets say I do a conjoint table like so:ProductCoupons--------------ProductCouponThe primary key would be on Product, Coupon. Should the index created by the primary key in MSSQL be clustered? Should I also create an index for each column for performance enhancements on joins? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 13:30:09
|
| Yes it should be clustered. Will you ever join on just coupon? If not, don't add the index as it will negatively impact DML statements.Adding an index to product would be considered a duplicate since product is the first column in the primary key index.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-26 : 13:37:16
|
| For each and every query I'll have to join both Product and Coupon because I'm searching for a coupon by the product, and the Coupon has the ability to be expired. So just Joining on coupon will tell me if there exists a coupon for a product, but not if its valid.So I should skip putting an index on the coupon? I thought that an index in this case would help speed up a join query because the join on ProductCoupons.Coupon would not utilize the ProductCoupons's primary key clustered index since that index's first field is "product". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-26 : 13:41:42
|
| If your JOIN is on both columns, then the optimizer will use the primary key index. I could be wrong, so see for yourself. Write a few queries (JOINing on both columns) in Query Analyzer and check out the execution plan. Prior to doing so, have the primary key created and the coupon index. Note which index it is using.Tara |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-26 : 13:42:05
|
| Also here's another design considerationt: there will be more products than there are coupons. With this in mind, the primary key field order should be reversed for performance correct?(PK) Coupon,Product |
|
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-26 : 13:43:14
|
| Ok I will try it out, thank you very much for your help tduggan. |
|
|
|
|
Indexing many to many relationship tables