Re: OT: SQL Server Table Design Question
On Mar 18, 2004, at 5:04 AM, Tangorre, Michael wrote: What is the proper, or preferred way of doing the following: I have three tables: T1 - PK A T2 - PK B T3 - PK AB (compound) Is it acceptable to add column C to T3 and make that the PK, and then add a unique constraint to AB? This would ease writing in writing of the WHERE clauses when updating and deleting and such... Thoughts? So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause Dick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
yeah, the question is a bit vague -Original Message- From: Dick Applebaum [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 8:44 AM To: CF-Talk Subject: Re: OT: SQL Server Table Design Question On Mar 18, 2004, at 5:04 AM, Tangorre, Michael wrote: What is the proper, or preferred way of doing the following: I have three tables: T1 - PK A T2 - PK B T3 - PK AB (compound) Is it acceptable to add column C to T3 and make that the PK, and then add aunique constraint to AB? This would ease writing in writing of the WHEREclauses when updating and deleting and such... Thoughts? So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause Dick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
Basically, I only gave you a partial story.. The rest of it is that the join tables are used in other relationships and the where conditions and join conditions are getting sloppy. In addition, a lot of the front end deals with dynamic forms and passing around a handful of IDs is getting cumbersome. Does that tip the scale either way? Mike So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
I tend to agree with Dave and opt for not adding Column C If I understand, you want to deal with C as an abstractionof the A-B relationship.I can see how passingC (instead of A B) would simplify form handling. But, you could pass AB just as easily as you can pass C. I think introducing C in your joins will just complicate them further as you will *still* need to specify T1.a = T3.a AND T3.b = T2.b relationship, somehow. Your code and SQL will be harder to read, explainand understand with this C thingie. So, it appears to be a tradeoff: denormalize added complexity vs convenience IMO, it isn't worth it HTH Dick On Mar 18, 2004, at 6:18 AM, Tangorre, Michael wrote: Basically, I only gave you a partial story.. The rest of it is that the join tables are used in other relationships and the where conditions and join conditions are getting sloppy. In addition, a lot of the front end deals with dynamic forms and passing around a handful of IDs is getting cumbersome. Does that tip the scale either way? Mike So, C is an identity or autonumber field? I have seen/done this, but not for the reasons you state -- could you expand on how using C would ease writing of the where clause [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
Just to play devil's advocate and also enjoy the unusual experience of disagreeing with Dave - your proposed approach of adding a surrogate key is our standard way of doing things. Even when the table is a simple intersection table consisting of nothing but 2 foreign keys, we always make the primary key an IDENTITY/autoincrement field. We never use a compound key as the primary key (though of course we use them as secondary keys). Why? Because it maximizes the amount of our code (including our automated ER-modelling-to-schema-generation app) that can work exactly the same way for different tables and even different applications. In conjunction with rigid standards for table column naming, it adds up to more productivity in the long run. Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
I agree, we do the same thing here. TK -Original Message- From: Nick de Voil [mailto:[EMAIL PROTECTED] Sent: Thursday, March 18, 2004 10:26 AM To: CF-Talk Subject: Re: OT: SQL Server Table Design Question Just to play devil's advocate and also enjoy the unusual experience of disagreeing with Dave - your proposed approach of adding a surrogate key is our standard way of doing things. Even when the table is a simple intersection table consisting of nothing but 2 foreign keys, we always make the primary key an IDENTITY/autoincrement field. We never use a compound key as the primary key (though of course we use them as secondary keys). Why? Because it maximizes the amount of our code (including our automated ER-modelling-to-schema-generation app) that can work exactly the same way for different tables and even different applications. In conjunction with rigid standards for table column naming, it adds up to more productivity in the long run. Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
On Mar 18, 2004, at 7:25 AM, Nick de Voil wrote: Just to play devil's advocate and also enjoy the unusual experience of disagreeing with Dave - your proposed approach of adding a surrogate key is our standard way of doing things. Even when the table is a simple intersection table consisting of nothing but 2 foreign keys, we always make the primary key an IDENTITY/autoincrement field. We never use a compound key as the primary key (though of course we use them as secondary keys). Why? Because it maximizes the amount of our code (including our automated ER-modelling-to-schema-generation app) that can work exactly the same way for different tables and even different applications. In conjunction with rigid standards for table column naming, it adds up to more productivity in the long run. Now, the above are valid reasons for including a separate key -- another might be that the T3 record contains intersection data, and/or it is sometimes meaningful to process this table by itself -- where the A B fields are reference data only; Dick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: OT: SQL Server Table Design Question
What are some of the downsides to this? Will I experience a performance hit should I use the additional key? Now, the above are valid reasons for including a separate key -- another might be that the T3 record contains intersection data, and/or it is sometimes meaningful to process this table by itself -- where the A B fields are reference data only; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL Server Table Design Question
You should not experience any performance hit. The only real downside is what Dave was talking about -- unless you have a good reason, why clutter up the database with an extra field and indexes -- the law of parsimony -- the simplest way is the best way. But, if you *do* have a good reason (as Nick did) then I am all for breaking the rules, when justified. Say, you have 3 dbs Customer Product ProductAndCustomer ProductAndCustomer is an intersection of the other 2 ProductAndCustomerIDPK Identity (autonumber) CustomerID FK ProductIDFK DateandTime OtherIntersectionData In our app, a customer can reorder a product many times so there can be many intersections for a given customer and product The combo of CustomerID and ProductID can't be used for a PK as it is not unique. We could use the timestamp as an uniquifer, but that's not a good choice. Setting up a separate ID field makes good sense. Further, when doing drill-downs, we can process the intersection records alone, or with one (or both) sides of the intersection. Dick On Mar 18, 2004, at 9:23 AM, Tangorre, Michael wrote: What are some of the downsides to this? Will I experience a performance hit should I use the additional key? Now, the above are valid reasons for including a separate key -- another might be that the T3 record contains intersection data, and/or it is sometimes meaningful to process this table by itself -- where the A B fields are reference data only; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
