Обсуждение: Re: [GENERAL] using ID as a key
Hi Simon, I have a practical reason and a theoretical reason. Practical reason - As an application develops over time, the database structure grows in unpredictable ways. My experience has been that this process begins during design. So suppose you have a table that is a mapping table between two other tables (the most tempting place to use a complex key). Later, it turns out that this mapping is something that needs to be referenced by several other tables. Now, every query that you write that joins this mapping table to its subordinate tables needs to reference both parts of the primary key. This problem has two facets: First, it is annoying to have to add the additional portion of the join to every query. Second, I guarantee that at some point one of your programmers will forget to add one of the parts of the join to one of his queries (this is VERY BAD, because this particular programming error is difficult to detect before the application goes into production, and it's a nuisance to track down once it is detected). Theoretical reason - Just as every variable in an application ought to serve one and only one purpose, every field in a database ought to serve one and only one purpose. And keeping track of records is a purpose in itself. Now, any time you have a complex key, the columns of the complex key have a meaning outside of keeping track of records. A change in that other meaning can affect that column's ability to serve its function as part of a record identifier. So, for goodness sake, just add the extra column. David Boerwinkle -----Original Message----- From: Simon Drabble <simon@eskimo.com> To: davidb@vectormath.com <davidb@vectormath.com> Cc: Postgres Group <pgsql-general@postgreSQL.org> Date: Friday, February 04, 2000 5:01 PM Subject: Re: [GENERAL] using ID as a key >On Fri, 4 Feb 2000 davidb@vectormath.com wrote: > >> >> Finally, since you proclaim that you are new to database design, I cannot >> pass up this opportunity strike out against the forces of evil. Unless you >> ABSOLUTELY cannot avoid it, never use more than one field as your primary >> key. Oh, it seems like a good idea at design time, but it's not. If you >> use >> complex primary keys, you and your programmers will regret it for the rest >> of >> the application's useful life. >> >> >> David Boerwinkle >> > > >David, > >What makes this such a bad idea? I am just now implementing a db that uses two >fields as primary key, and I'd like to know what I'm letting myself and >colleagues in for! I can only guess that it will slow down queries, but that's >not something a programmer should necessarily be concerned with.. > >(Thankfully it is not too late to change the design if need be :) ) > >Simon. > > >-- > "Linux - open doors, not windows." > > Simon Drabble It's like karma for your brain. > simon@eskimo.com > > >************ >
On Fri, 4 Feb 2000 davidb@vectormath.com wrote: > Hi Simon, > > I have a practical reason and a theoretical reason. > > Practical reason - As an application develops over time, the database > structure grows in unpredictable ways. My experience has been that this > process begins during design. So suppose you have a table that is a > mapping table between two other tables (the most tempting place to use a > complex key). Later, it turns out that this mapping is something that needs > to > be referenced by several other tables. Now, every query that you write that > joins this mapping table to its subordinate tables needs to reference both > parts of the primary key. This problem has two facets: First, it is > annoying to > have to add the additional portion of the join to every query. Second, I > guarantee that at some point one of your programmers will forget to add one > of the parts of the join to one of his queries (this is VERY BAD, because > this > particular programming error is difficult to detect before the application > goes > into production, and it's a nuisance to track down once it is detected). > > Theoretical reason - Just as every variable in an application ought to serve > one and only one purpose, every field in a database ought to serve one and > only one purpose. And keeping track of records is a purpose in itself. > Now, > any time you have a complex key, the columns of the complex key have a > meaning outside of keeping track of records. A change in that other meaning > can affect that column's ability to serve its function as part of a record > identifier. > > So, for goodness sake, just add the extra column. > > David Boerwinkle > David, Both reasons you mentioned make sense, and I am almost convinced to change my design.. I say almost because I will need to look at it again to make sure that modifying the key structure won't hamper selects (there may be times we need to perform lookups on only one half of the compound key, e.g. if key = col1+col2, we might need to lookup on col1 only - for our particular values this could prove troublesome to do with the like operator) Anyway, thanks for your answer. Simon. -- "Linux - open doors, not windows." Simon Drabble It's like karma for your brain. simon@eskimo.com
Simon Drabble wrote: I agree with everything Simon says (hmmm, there's a game in that) and would add: It's very likely that at some time you will change your database design such that you will want to add or remove a field or two that uniquely identify the record (hmmm, person's first and last name not enough anymore, need to add social security number...). Now, all those joins (and foreign keys!) need to be changed to reflect the additional field. Yes, I have been bitten by that. The other way (unique ID number), nothing in your queries changes. > > On Fri, 4 Feb 2000 davidb@vectormath.com wrote: > > > Hi Simon, > > > > I have a practical reason and a theoretical reason. > > > > Practical reason - As an application develops over time, the database > > structure grows in unpredictable ways. My experience has been that this > > process begins during design. So suppose you have a table that is a > > mapping table between two other tables (the most tempting place to use a > > complex key). Later, it turns out that this mapping is something that needs > > to > > be referenced by several other tables. Now, every query that you write that > > joins this mapping table to its subordinate tables needs to reference both > > parts of the primary key. This problem has two facets: First, it is > > annoying to > > have to add the additional portion of the join to every query. Second, I > > guarantee that at some point one of your programmers will forget to add one > > of the parts of the join to one of his queries (this is VERY BAD, because > > this > > particular programming error is difficult to detect before the application > > goes > > into production, and it's a nuisance to track down once it is detected). > > > > Theoretical reason - Just as every variable in an application ought to serve > > one and only one purpose, every field in a database ought to serve one and > > only one purpose. And keeping track of records is a purpose in itself. > > Now, > > any time you have a complex key, the columns of the complex key have a > > meaning outside of keeping track of records. A change in that other meaning > > can affect that column's ability to serve its function as part of a record > > identifier. > > > > So, for goodness sake, just add the extra column. > > > > David Boerwinkle > > > > David, > > Both reasons you mentioned make sense, and I am almost convinced to change > my design.. I say almost because I will need to look at it again to make > sure that modifying the key structure won't hamper selects > (there may be times we need to perform lookups on only one half of the > compound key, e.g. if key = col1+col2, we might need to lookup on col1 only - > for our particular values this could prove troublesome to do with the like > operator) > > Anyway, thanks for your answer. > > Simon. > > -- > "Linux - open doors, not windows." > > Simon Drabble It's like karma for your brain. > simon@eskimo.com > > ************ -- Microsoft forced OEMs to ignore consumer demand for a browserless version of Windows. -- Judge Thomas Penfield Jackson Your most unhappy customers are your greatest source of learning. -- Bill Gates