Thread: surrogate key or not?
Hi, I have a database that has types in them with unique names. These types are referenced from other tables through a surrogate integer key. I'm now wondering if I should eliminate that surrogate key and just use the name as the primary key. Afaiu, surrogate keys are primarily there to make joining tables or otherwise searching for a record faster, because it's faster to compare two integers than it is to compare two strings. Now when I want to search for a type in types or another table that references types(type_id), under what circumstances is it advisable to have a surrogate integer key and not use the unique type name? Is searching for an integer as fast as is searching for a string when both have an index? How many records in the type table do I need to make a surrogate key a not unsignificantly faster way to retrieve a row? What about joins? Are these the right questions? Thanks. -- Markus Bertheau <twanger@bluetwanger.de>
Hi, for my 2c worth, performance is the least important of the things you need to consider regarding use of surrogate keys. I use surrogate keys for all situations except the simplest code/description tables, and this is only when the code has no meaning to the application. If there is any possibility that you will want to update or re-use codes (attaching a different meaning to them) then surrogate keys are the way to go.. Thus I see it more as an issue of business logic than performance. There are of course many other considerations with relational theory and stuff like that which you could debate endlessly. I expect that googling on "surrogate keys" would yeild interesting results. Regards Iain ----- Original Message ----- From: "Markus Bertheau" <twanger@bluetwanger.de> To: <pgsql-sql@postgresql.org> Sent: Tuesday, July 20, 2004 9:16 PM Subject: [SQL] surrogate key or not? > Hi, > > I have a database that has types in them with unique names. These types > are referenced from other tables through a surrogate integer key. I'm > now wondering if I should eliminate that surrogate key and just use the > name as the primary key. Afaiu, surrogate keys are primarily there to > make joining tables or otherwise searching for a record faster, because > it's faster to compare two integers than it is to compare two strings. > > Now when I want to search for a type in types or another table that > references types(type_id), under what circumstances is it advisable to > have a surrogate integer key and not use the unique type name? Is > searching for an integer as fast as is searching for a string when both > have an index? How many records in the type table do I need to make a > surrogate key a not unsignificantly faster way to retrieve a row? What > about joins? Are these the right questions? > > Thanks. > > -- > Markus Bertheau <twanger@bluetwanger.de> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Markus, Iain, > Thus I see it more as an issue of business logic than performance. There are > of course many other considerations with relational theory and stuff like > that which you could debate endlessly. I expect that googling on "surrogate > keys" would yeild interesting results. Frankly, I couldn't disagree more. This is why it was so problematic for the SQL committee to enshrine "primary keys" and sequences in the standard; it mis-educates database designers into believing that surrogate keys are somehow part of the data model. They are most decidely NOT. Given: Surrogate keys, by definition, represent no real data; Given: Only items which represent real data have any place ina data model Conclusion: Surrogate keys have no place in the data model There are, in fact, three very good reasons to use surrogate keys, all of which are strictly due to limitations of technology; that is, implementation and performance issues, NOT business logic. They are: 1) Convenience: It's very annoying to have to refer to a 4-column foriegn key whenever you do a join in queries or want to delete a record, as well as tracking a 4-element composite in your client software. 2) Performance: INT and BIGINT data types are among the most compact and efficient stored in most RDBMSs. So using anything else as a key would likely result in a loss of performance on large-table joins. 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and updates. Some RDBMSs do not support CASCADE, forcing the client software to fix all the dependant rows. This means that DBAs are very reluctant to use columns which change frequently as join keys. All three of these implementation issues are, at least in theory, surmountable. For example, Sybase overcame problems (1) and (3) by creating an automated, system-controlled hash key based on the table's real key. This was a solution endorsed by E.F. Codd in the mid-90's when he came to regret his promotion of the "Primary Key" idea in the SQL standard. Now, you're probably wondering "why does this guy regard surrogate keys as a problem?" I'll tell you: I absolutely cannot count the number of "bad databases" I've encountered which contained tables with a surrogate key, and NO REAL KEY of any kind. This makes data normalization impossible, and cleanup of the database becomes a labor-intensive process requiring hand-examination of each row. -- -Josh BerkusAglio Database SolutionsSan Francisco
Markus, Oh, so you want USEFUL answers. OK. > Now when I want to search for a type in types or another table that > references types(type_id), under what circumstances is it advisable to > have a surrogate integer key and not use the unique type name? When using the actual name will be a performance problem. > Is > searching for an integer as fast as is searching for a string when both > have an index? Not usually, no. The index on the text values will simply be larger than the one on 4-byte INTs, which means it's "slower", assuming you run out of memory some of the time. If your whole DB fits in RAM, it's not worth worrying about. > How many records in the type table do I need to make a > surrogate key a not unsignificantly faster way to retrieve a row? It needs to be large enougth that the difference in data types makes a difference in whether or not it will fit into sort_mem, and how likely it is to be already cached in memory. > What > about joins? Double jeopardy; you're using the column twice so double the storage difference. Otherwise, it's just the same issue; does it still fit in RAM or not? > Are these the right questions? Also you'll want to consider the speed of CASCADE operations whenever a type_name changes. If these changes occur extremely infrequently, then you can ignore this as well. -- -Josh BerkusAglio Database SolutionsSan Francisco
Thnx, Josh you are very helpful. > There are, in fact, three very good reasons to use surrogate keys, all of > which are strictly due to limitations of technology; that is, > implementation and performance issues, NOT business logic. They are: > > 1) Convenience: It's very annoying to have to refer to a 4-column foriegn > key whenever you do a join in queries or want to delete a record, as well > as tracking a 4-element composite in your client software. > > 2) Performance: INT and BIGINT data types are among the most compact and > efficient stored in most RDBMSs. So using anything else as a key would > likely result in a loss of performance on large-table joins. > > 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and > updates. Some RDBMSs do not support CASCADE, forcing the client software > to fix all the dependant rows. This means that DBAs are very reluctant to > use columns which change frequently as join keys. > Now, you're probably wondering "why does this guy regard surrogate keys as > a problem?" I'll tell you: I absolutely cannot count the number of "bad > databases" I've encountered which contained tables with a surrogate key, > and NO REAL KEY of any kind. This makes data normalization impossible, > and cleanup of the database becomes a labor-intensive process requiring > hand-examination of each row. The surrogate keys is a real big problem. But i want to add another two reasons to have a surrogate keys 4) Replication: to identify an object of ANY type (record of any table regardless to datamodel), to store lists of deleted or modified objects (regardless to datamodel) 5) Making a primary key: if there is no real key at all. the sentence (5) is debatable. in theory every relation has a real key, but in the practice we have historical datamodels without PK !!! it is impossible but it exists. For example: Here in Russia we have a lot of different but identically named streets within one city. They has absoluetly identical attributes. Historically only human not machines work on that datamodel and they all used to call such streets by name adding some spechial non-formal explainations, for example: "deliver this message please to the house 35 on the Green street, that is to the west of the center of the city." "deliver this message please to the house 12 on the Green street, that is shortest of the all Green streets in the town." Another exaple is a table of user messages: user has a number of messages they have no mean attribute and also could be identical, so you force this user to numbering his messages, or number them yourself.
В Срд, 21.07.2004, в 09:44, sad пишет: > 4) Replication: to identify an object of ANY type (record of any table > regardless to datamodel), to store lists of deleted or modified objects > (regardless to datamodel) That sounds like a datamodel on data that belongs to another data model, and 5) applies. > 5) Making a primary key: if there is no real key at all. When there's no key at all, there can't be a surrogate key, as I understand it. In such cases a generated unique number comes in handy, and it's a real primary key and no surrogate key. Спасибо -- Markus Bertheau <twanger@bluetwanger.de>
On Wednesday 21 July 2004 14:58, Markus Bertheau wrote: > > 4) Replication: to identify an object of ANY type (record of any table > > regardless to datamodel), to store lists of deleted or modified objects > > (regardless to datamodel) > > That sounds like a datamodel on data that belongs to another data model, that's right, these surr. keys actually belong to a metadata model, but it is also useful to references of a data model. and they are used this way. > > 5) Making a primary key: if there is no real key at all. > > When there's no key at all, there can't be a surrogate key, as I > understand it. In such cases a generated unique number comes in handy, > and it's a real primary key and no surrogate key. ...right. in case (5) we construct another datamodel indeed. with an artifichial real key :-) P.S. r u from Russia ?
> All three of these implementation issues are, at least in theory, > surmountable. For example, Sybase overcame problems (1) and (3) by creating > an automated, system-controlled hash key based on the table's real key. This > was a solution endorsed by E.F. Codd in the mid-90's when he came to regret > his promotion of the "Primary Key" idea in the SQL standard. I've often wondered if the OID was intended to be something similar. You specify: CREATE TABLE abc (key varchar(120) PRIMARY KEY);CREATE TABLE bcd (key varchar(120) REFERENCES abc); PostgreSQL was actually supposed to create: abc(oid oid, key varchar(120) PRIMARY KEY);bcd(key oid REFERENCES abc(oid)); Then muck around with the queries to resolve the actual varchar() name when required.
Sad, First of all, please excuse me if I've misunderstood you below because of translation issues. You'll find I'm rather strident, but it's because the reasons you're presenting, or seem to be, are excuses for bad database design I hear every day on the job, and end up having to fix when they go wrong. Now, to reply to your comments: > 4) Replication: to identify an object of ANY type (record of any table > regardless to datamodel), to store lists of deleted or modified objects > (regardless to datamodel) Only if the Replication product requires it. Note that our new replication engine, Slony-I, does *not* require surrogate keys; it can handle multi-column primary keys. I'm not sure about the other replication products, they may be able to as well. I think what you're talking about is a GUID (Global Unique Identifier). Once again, while most GUIDs are based on random number theory, the ideal GUID would be a unique combination of the Row Key, the version, and the server identification -- making it "real data" instead of a random number that tells you nothing about the row. For performance, this combination might be combined in a hash. In other words, I think your 4th point is actually part of Point (1), convenience. > 5) Making a primary key: if there is no real key at all. No, no, no, a thousand times NO. Data without a real key is NOT DATA. It is just garbage. I manage or consult on more than twenty production databases for my clients. Every single one of those databases has a real unique key on every single table. The ONLY exception is "holding tables" for incoming bulk data, some of which will be retained as data and some of which will discarded as garbage -- and key uniqueness is the primary test of what is and isn't data. > the sentence (5) is debatable. in theory every relation has a real key, but > in the practice we have historical datamodels without PK !!! it is > impossible but it exists. Only because you have bad historical databases. This is not an excuse, it is a reason to fix them. > For example: > Here in Russia we have a lot of different but identically named streets > within one city. They has absoluetly identical attributes. Historically > only human not machines work on that datamodel and they all used to call > such streets by name adding some spechial non-formal explainations, for > example: > "deliver this message please to the house 35 on the Green street, that is > to the west of the center of the city." > "deliver this message please to the house 12 on the Green street, that is > shortest of the all Green streets in the town." This is a PERFECT example of the evil of surrogate keys. You had a data problem, and instead of solving it, you chose to lean on surrogate keys like a crutch. Here's what you have in your database now: Streets ID Street Name 345 Green Street 2019 Green Street 5781 Green Street Key: ID??? How, exactly, do you expect to distinguish the above 3 "green streets" from each other? How do you expect to your users to know that 345 is West Green Street and 5781 is the shortest in town? How are they supposed to choose between the three? How do you know there are actually 3 and maybe not just two or even one? The ID 5781 isn't exactly informative; in fact, it's meaningless. It's GARBAGE. Look, real data, on a row-by-row basis, is a POSTULATE. It is should describe adequately the portion of the world you are trying to represent. Automated surrogate keys are NOT part of these postulates since they convey no meaningful information. This is what you currently have: There is a street named GREEN STREET. There is a street named GREEN STREET. There is a street named GREEN STREET. As you can see by rendering it in English, those three statements are damned confusing. Are there three streets named Green Street, or is there only one? Either could be true. We don't know, the data doesn't tell us. What you need is: Streets ID Street Name Location 345 Green Street West Side of City 2019 Green Street In Front of Consulate 5781 Green Street Shortest in Town Key: Street Name, Location This gives you much more meaningful data: There is a street named GREEN STREET which is on the WEST SIDE OF THE CITY. There is a street named GREEN STREET which is IN FRONT OF THE CONSULATE. There is a street named GREEN STREET which is the SHORTEST IN TOWN. This tells the user which Green Street he wants to select. It also gives us a fair indication that there are 3 Green Streets, and not one or two. And if there is duplication -- for example, if the street in front of the consulate is also the shortest in town -- then we can determine this and correct it in a minute or less by looking at a map. I'm not just picking on you. I'm saying this because it's happened to me and was a costly error for both me and the client. Several years ago, we were doing a scheduling database, and the client messed around with the data model a lot. In the process, we mistakenly eliminated the Events table's (the largest and most important table in the database) real unique key. But because we were "leaning" on a surrogate key, we didn't notice until the database was in production. Then, after 6 months of scheduling, the client began to suspect that duplicate Events were creeping into the database. But without a real, unique key, we discovered that we could not figure out whether or not two events with similar information were duplicates or not! The Event_ID told us nothing. This was a real, critical problem because each Event required the attendance of an attorney and there were never enough attorneys to go around. -- Josh Berkus Aglio Database Solutions San Francisco
Hi Josh, Considering that I generally agree with your comments (in this and your later posts) I'd say I didn't make myself clear in my brief comment. Of the considerations: performance, convenience and business logic, I personally rate performance as the lowest priority. The convenience part has been well covered in other posts and is second most in my opinion.. By business logic I was thinking of a situation we had on an old db where employees were idenitfied by codes. These codes were transmitted all over the database (as you can imagine) which meant that the codes couldn't be recycled as employees came and left. The answer was to introduce an integer based auto-generated key. That way the data associated with an ex-employee is associated with that employee, not with the code. It may be that this really a convenience issue as opposed to business logic, but in my thinking the code was data, not a primary key - it is not even an alternate key (if ex-employees have their code nulled it can be made "unique" though). Perhaps the term surrogate key doesn't apply in this case, I have to admit that I'm not 100% on terminalogy here. In the end, accurately representing the business logic is most importent, and use of a contrived sequential key shouldnt preclude the use of unique constraints where needed so the problem you described of duplicate events should never have happened anyway. This is a great topic though, I find it really interesting. Regards Iain ----- Original Message ----- From: "Josh Berkus" <josh@agliodbs.com> To: "Iain" <iain@mst.co.jp>; "Markus Bertheau" <twanger@bluetwanger.de>; <pgsql-sql@postgresql.org> Sent: Wednesday, July 21, 2004 4:00 PM Subject: Re: [SQL] surrogate key or not? > Markus, Iain, > > > Thus I see it more as an issue of business logic than performance. There are > > of course many other considerations with relational theory and stuff like > > that which you could debate endlessly. I expect that googling on "surrogate > > keys" would yeild interesting results. > > Frankly, I couldn't disagree more. This is why it was so problematic for the > SQL committee to enshrine "primary keys" and sequences in the standard; it > mis-educates database designers into believing that surrogate keys are > somehow part of the data model. They are most decidely NOT. > > Given: Surrogate keys, by definition, represent no real data; > Given: Only items which represent real data have any place in > a data model > Conclusion: Surrogate keys have no place in the data model > > There are, in fact, three very good reasons to use surrogate keys, all of > which are strictly due to limitations of technology; that is, implementation > and performance issues, NOT business logic. They are: > > 1) Convenience: It's very annoying to have to refer to a 4-column foriegn key > whenever you do a join in queries or want to delete a record, as well as > tracking a 4-element composite in your client software. > > 2) Performance: INT and BIGINT data types are among the most compact and > efficient stored in most RDBMSs. So using anything else as a key would > likely result in a loss of performance on large-table joins. > > 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and > updates. Some RDBMSs do not support CASCADE, forcing the client software to > fix all the dependant rows. This means that DBAs are very reluctant to use > columns which change frequently as join keys. > > All three of these implementation issues are, at least in theory, > surmountable. For example, Sybase overcame problems (1) and (3) by creating > an automated, system-controlled hash key based on the table's real key. This > was a solution endorsed by E.F. Codd in the mid-90's when he came to regret > his promotion of the "Primary Key" idea in the SQL standard. > > Now, you're probably wondering "why does this guy regard surrogate keys as a > problem?" I'll tell you: I absolutely cannot count the number of "bad > databases" I've encountered which contained tables with a surrogate key, and > NO REAL KEY of any kind. This makes data normalization impossible, and > cleanup of the database becomes a labor-intensive process requiring > hand-examination of each row. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco
Josh, I reckon you are the one in the know so I'll take advantage of that and ascertain myself of your advice. I am the primary designer for the database schema of GnuMed (www.gnumed.org) - a practice management application intended to store medical data. Obviously we wouldn't want ambigous data. I have until now used surrogate primary keys on all table like so: create table diagnosis ( pk serial primary key, fk_patient integernot nullreferences patient(pk)on update cascadeon deletecascade, narrative textnot null, unique(fk_patient, narrative) ); Note that fk_patient would not do for a primary key since you can have several diagnoses for a patient. However, the combination of fk_patient and narrative would, as is implied by the unique() constraint. For fear of having the real primary key change due to business logic changes I have resorted to the surrogate key. Short question: Is this OK re your concerns for using surrogates, eg. using a surrogate but making sure that at any one time there *would* be a real primary key candidate ? This would amount to: > Streets > ID Street Name Location > 345 Green Street West Side of City > 2019 Green Street In Front of Consulate > 5781 Green Street Shortest in Town Key: ID UNIQUE: Key, Location Is that OK ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Kasten, > I have until now used surrogate primary keys on all table like > so: <snip> > Short question: Is this OK re your concerns for using > surrogates, eg. using a surrogate but making sure that at any > one time there *would* be a real primary key candidate ? Yes, this is an example of that. You have a long text field as part of the key, and that would kill you performance-wise if diagnosis was referred to in other tables and joined in views. Keep in mind, though, that if a table is an "end node"; that is, if its PK is not used as an FK by any other table, then worries about the performance of keys and size of indexes are unfounded. In fact, for such tables, the surrogate key is a performance drag; it adds a column and an index which are not needed. Now, addressing your table, I would have concerns other than the use of primary keys. I suggest humbly that your data model/business logic may need some development: create table diagnosis ( pk serial primary key, fk_patient integer not null references patient(pk) on update cascade on delete cascade, narrative text not null, unique(fk_patient, narrative) ); This was obviously created so that a patient could have multiple diagnoses. However, there is no information in the table to indicate *why* there are multiple diagnoses. And you are using a real key based on a long text field; always hazardous, as there are many ways to phrase the same information and duplication is likely. To do it in english, your postulates look like: PATIENT 67 was given a diagnosis of WATER ON THE KNEE. PATIENT 456 was given a diagnosis of ACUTE HYPOCHONDRIA. But this is a bit sketchy. Who made these diagnoses? When did they make them? Why? This table could carry a *lot* more information, and should (sql is shorthand) create table diagnosis ( pk serial primary key, fk_patient integer references patient(pk), fk_visit integer referencesvisits(pk), fk_complaint integer references complaints(pk) fk_staff integer references medical_staff(pk) narrative text, unique(fk_patient, fk_visit, fk_complaint, fk_staff) ); Then your postulates become *much* more informative: PATIENT 67 was given a diagnosis by STAFF MEMBER 12 on his VISIT #3 in response to NOT BEING ABLE TO WALK of WATER ON THEKNEE PATIENT 456 was given a diagnosis by STAFF MEMBER 19 on his VISIT #192 in response to THE CREEPY-CRAWLIES of ACUTE HYPOCHONDRIA It also allows you to establish a much more useful key; it's reasonable to expect that a single staff member on one visit in response to one complaint would only give one diagnosis. Otherwise, you have more than database problems. And it prevents you from having to rely on a flaky long text key. -- Josh Berkus Aglio Database Solutions San Francisco
I want to add some notes > create table diagnosis ( > pk serial primary key, > fk_patient integer > not null > references patient(pk) > on update cascade > on delete cascade, > narrative text > not null, > unique(fk_patient, narrative) > ); 1) a sequence generates INT8 values (in general) and you have INT4 field to refer to a serial field. 2) narrative TEXT is very bad to check uniquness becauce it is non-formal human-generated native-language text. so it contains missprints, it may be rephrased many ways with the same meaning. 3) afaik a diagnosis doesn't belong to a patient, it belongs to a History, and History is marked with a date and status and belongs to a patient. do not treat my words as The Truth.
Josh, sad, > create table diagnosis ( > pk serial primary key, > fk_patient integer > not null > references patient(pk) > on update cascade > on delete cascade, > narrative text > not null, > unique(fk_patient, narrative) > ); > > This was obviously created so that a patient could have multiple diagnoses. > However, there is no information in the table to indicate *why* there are > multiple diagnoses. Because there is no information to be had on this fact. The patient IS afflicted by such or she is not. There is no why. > And you are using a real key based on a long text > field; Yes, but for various concerns I am not using it as the primary key, just making sure it is unique. I was just trying to ascertain myself that this is OK to do from a database insider point of view. > always hazardous, as there are many ways to phrase the same > information and duplication is likely. But that is at the discreetion of the user/doctor and nothing that can be enforced at the DB level (no, don't start thinking about coding systems/classifications). > To do it in english, your postulates look like: > > PATIENT 67 was given a diagnosis of WATER ON THE KNEE. > PATIENT 456 was given a diagnosis of ACUTE HYPOCHONDRIA. Hm, I don't see anything wrong with that (I'm a doctor). The plain information that Patient 456 is known to have suffered bouts of ACUTE HYPOCHONDRIA is invaluable when dealing with an agitated, psychically decompensated, hyperventilating patient 456. > But this is a bit sketchy. Who made these diagnoses? I may or may not care. Our actual schema does, of course, carry that information. > When did they make them? We'd be happy if we always knew. >Why? That's of marginal concern, actually, and the answer just flows from the narrative of the medical record. But even if there's no narrative there the "fact" alone helps. > create table diagnosis ( > pk serial primary key, > fk_patient integer references patient(pk), > fk_visit integer references visits(pk), > fk_complaint integer references complaints(pk) Nope, this doesn't belong here AT ALL from a medical point of view. Diagnoses and complaints don't have any rational relationship. This is life. > fk_staff integer references medical_staff(pk) > narrative text, > unique(fk_patient, fk_visit, fk_complaint, fk_staff) > ); And in fact our real tables ARE pretty much like that :-) > PATIENT 67 was given a diagnosis by STAFF MEMBER 12 on his VISIT #3 > in response to NOT BEING ABLE TO WALK of WATER ON THE KNEE > PATIENT 456 was given a diagnosis by STAFF MEMBER 19 on his VISIT #192 > in response to THE CREEPY-CRAWLIES of ACUTE HYPOCHONDRIA That'd by a psychosis ;-) > It also allows you to establish a much more useful key; it's reasonable to > expect that a single staff member on one visit in response to one complaint > would only give one diagnosis. Entirely false and a possible sign of inappropriate care. > Otherwise, you have more than database > problems. And it prevents you from having to rely on a flaky long text key. Flaky long text is what kept people reasonably well in health for the last, what, five thousand years ? I rely on it countless times every single day. BTW, our full schema is here: http://www.hherb.com/gnumed/schema/ Lot's of it isn't in the state yet where we want it but we are getting there - or so I think. Karsten Hilbert, MD, PhD Leipzig, Germany -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Friday 23 July 2004 12:27 pm, Karsten Hilbert wrote: > BTW, our full schema is here: > > http://www.hherb.com/gnumed/schema/ i was looking at your schema. i'm not a database pro, but in master tables i see you have made the serial id as the primary key. i do it this way: id serial unique name varchar(25) not null primary key is name - after all, you are going to search this on name arent you? or is there some advantage in doing it your way? also, how did you get that neatly formatted output of the schema? -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org
On Jul 23, 2004, at 4:57 PM, Kenneth Gonsalves wrote: > On Friday 23 July 2004 12:27 pm, Karsten Hilbert wrote: > >> BTW, our full schema is here: >> >> http://www.hherb.com/gnumed/schema/ > > i was looking at your schema. i'm not a database pro, but in master > tables i > see you have made the serial id as the primary key. i do it this way: > > id serial unique > name varchar(25) not null > primary key is name - after all, you are going to search this on name > arent > you? or is there some advantage in doing it your way? Kenneth, You appear to be misunderstanding the purpose of a primary key. A primary key is used to ensure there is a way to identify each row uniquely. It is quite independent of which columns you may or may not want to search on. If name is not going to be necessarily unique in the table, it isn't a primary key. Michael Glaesemann grzm myrealbox com
On Friday 23 July 2004 03:29 pm, Michael Glaesemann wrote: > > You appear to be misunderstanding the purpose of a primary key. A > primary key is used to ensure there is a way to identify each row > uniquely. It is quite independent of which columns you may or may not > want to search on. If name is not going to be necessarily unique in the > table, it isn't a primary key. ive not misunderstood anything. this is one of the tables in question: address_type id serial PRIMARY KEY name text UNIQUE NOT NULL i think it is self explanatory -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org
On Jul 23, 2004, at 6:00 PM, Kenneth Gonsalves wrote: > On Friday 23 July 2004 03:29 pm, Michael Glaesemann wrote: > >> >> You appear to be misunderstanding the purpose of a primary key. A >> primary key is used to ensure there is a way to identify each row >> uniquely. It is quite independent of which columns you may or may not >> want to search on. If name is not going to be necessarily unique in >> the >> table, it isn't a primary key. > > ive not misunderstood anything. this is one of the tables in question: > > address_type > id serial PRIMARY KEY > name text UNIQUE NOT NULL > > i think it is self explanatory In the example you originally gave, there is no indication of name being a primary key: On Friday 23 July 2004 12:27 pm, Karsten Hilbert wrote: > id serial unique > name varchar(25) not null > primary key is name - after all, you are going to search this on name > arent > you? or is there some advantage in doing it your way? Also, your explanation "after all, you are going to search..." did not mention row uniqueness at all. Sorry if this is not what you meant, but I can only go by what you've written. Michael Glaesemann grzm myrealbox com
On Friday 23 July 2004 04:47 pm, Michael Glaesemann wrote: > > id serial unique > > name varchar(25) not null > > primary key is name - after all, you are going to search this on name > > arent > > you? or is there some advantage in doing it your way? > > Also, your explanation "after all, you are going to search..." did not > mention row uniqueness at all. Sorry if this is not what you meant, but > I can only go by what you've written. ok, i'll rephrase the whole thing: i have a master table with two fields: id serial unique name varchar not null (and will be unique) i always make 'name' the primary key, and since it is the primary key, i dont explicitly specify it as unique, and after postgres 7.3 came out, i have added the 'unique' constraint to the 'id' on looking at the gnumed schema, i saw that although 'name' was unique, the serial key, 'id' was made the primary key. So i wondered why and whether there were advantages in doing it this way. -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org
Regarding natural or surrogate keys...
It is often possible that a table definition does not depict
reality, meaning that the specification given at table design
phase was wrong, (or was later proved wrong).
I had a table "parts" like
\d parts Table "public.parts" Column | Type |
Modifiers
-----------------+-----------------------+-------------------------------------------------------partno |
charactervarying(20) | not nullmachtypeclsid | integer | not nullpartclsid | integer
| not nullpicture | bytea |instructions | bytea |
.............
Indexes: "parts_ukey" primary key, btree (partno, machtypeclsid)
Foreign-key constraints: "$2" FOREIGN KEY (partclsid) REFERENCES partclasses(id) "$1" FOREIGN KEY (machtypeclsid)
REFERENCESmachtypesclasses(id)
Initially i was told, and found pretty natural, that a machine type
and a part number would fully identify a part.
Only to find out later, after a bunch of apps was written, that some
makers, described, in the same machine type,
different parts (on differnt drawing numbers i.e. pages in maker's manual)
with the same part numbers.
The irony here, is that this was the only instance of natural primary keys
in my whole schema.
I had then to write a script to convert the table itself, as well as
children tables to it, to the new schema.
Now the table looks like:
\d parts Table "public.parts" Column | Type |
Modifiers
-----------------+-----------------------+-------------------------------------------------------partno |
charactervarying(20) | not nullmachtypeclsid | integer | not nullpartclsid | integer
| not nullpicture | bytea |instructions | bytea |
..........id | integer | not null default
nextval('public.parts_id_seq'::text)drgno | character varying(20) |
Indexes: "parts_pkey" primary key, btree (id) "parts_ukey" unique, btree (drgno, partno, machtypeclsid)
Foreign-key constraints: "$2" FOREIGN KEY (partclsid) REFERENCES partclasses(id) "$1" FOREIGN KEY (machtypeclsid)
REFERENCESmachtypesclasses(id)
So, now if they decide, that apart from drgno (drawing number),
a new field "revision" must be added, that will be needed to identify
"uniquely" the part, along with drgno,partno,machtypeclsid,
it wouldn't mean a lot of changes.
In other words, with surrogate keys, you eliminate the chance
that your original design was flawed due to lack of important
initial knowledge.
A designer in the majority of cases, gets his input from people
of the subject being modeled.
Often these people fail to have the whole image described
when giving specs, hence all the trouble.
--
-Achilleus
Am Freitag, 23. Juli 2004 12:02 schrieb Kenneth Gonsalves: > ok, i'll rephrase the whole thing: > > i have a master table with two fields: > id serial unique > name varchar not null (and will be unique) > > i always make 'name' the primary key, and since it is the primary key, i > dont explicitly specify it as unique, and after postgres 7.3 came out, i > have added the 'unique' constraint to the 'id' > > on looking at the gnumed schema, i saw that although 'name' was unique, the > serial key, 'id' was made the primary key. So i wondered why and whether > there were advantages in doing it this way. Does your question relates to surrogate vs natural keys discussion? I made some researches a few months ago and read a lot including: http://www.intelligententerprise.com/print_article_flat.jhtml?article=/030320/605celko1_1.jhtml http://www.winnetmag.com/Articles/Print.cfm?ArticleID=5113 http://www.dbpd.com/vault/9805xtra.htm http://www.sapior.com/Resources/Surrogate_Keys/surrogate_keys.html http://www.bits-pilani.ac.in/dlp-home/online/studymat/sszg515/lecture3.htm http://www.bcarter.com/intsurr1.htm i decided to use natural keys wherever possible and i have many primary keys spanning up to 4 attributes. And it works really fine. Performance is great, the schema is easy to use and i am so glad to use the natural key approach. Writing SQL queries and php code is much easier! By now i try to avoid surrogate keys (like with SERIALs datatype) wherever possible. Most articles advocate surrogate keys and at first it looks like an advantage in a web environment because selecting and transmitting a multi-column primary key in a form field ist very difficult. Imagine a <select> element, but you have only one value to be returned. My trick here is to have the primary keys used in the select element saved in a session array and using the session array index as a select element value. But the strongest argument for me is: All candidate keys have to be unique anyway. And postgresql builds an index anyway for every UNIQUE key, because thats the way postgresql checks uniqueness. So why add another artifical key with another index when you can use the one which is given anyway. Think of usergroups identified by name and members which are identified by user groups name and email adress, then you've got the pseudo schema create table usergroups ( ug_name text, CONSTRAINT uq_ug UNIQUE (ug_name) ); create table members ( ug_name text, mb_email text, CONSTRAINT uq_mb UNIQUE (ug_name, mb_email), CONSTRAINT fk_ug_nameFOREIGN KEY ug_name REFERENCES usergoups (ug_name) ); so you have to indexes uq_mb and uq_ug anyway. So why dont use them as Primary Keys?? With two more attribute for a surrogate key like ug_id SERIAL PRIMARY KEY in table usergroups and mb_id SERIAL PRIMARY KEY you have additional 4 bytes to store for each table row and one more index for each table. So my conclusion is: i dont see any benefit in using surrogate keys. But this must be wrong because so many people are using and advocating surrogate keys. They might only be useful in circumstances where no natural key is given. kind regards, janning
Michael Glaesemann <grzm@myrealbox.com> writes:
> You appear to be misunderstanding the purpose of a primary key. A
> primary key is used to ensure there is a way to identify each row
> uniquely. It is quite independent of which columns you may or may not
> want to search on. If name is not going to be necessarily unique in the
> table, it isn't a primary key.
The other standard reason for using a made-up value as primary key is
that it's under your control and you can guarantee it isn't going to
change: one record will have the same primary key for its entire life,
which vastly simplifies handling of foreign keys and so forth.
If you use any real-world datum as primary key you necessarily buy into
tracking changes in that real-world value. And handling duplicates.
Names are certainly not unique, and they do change (marriage, etc) and
people do miskey them and then expect to be able to fix the error later.
As Achilleus' nearby story shows, you can have these problems (certainly
the misentry part) even with imported data that is allegedly someone
else's primary key; part numbers, USA social-security numbers, etc.
regards, tom lane
Achilleus, > In other words, with surrogate keys, you eliminate the chance > that your original design was flawed due to lack of important > initial knowledge. Well, you don't *eliminate* it, but you do decrease it. I'd say, yes, this is an important 4th reason: 4) Your spec may be incorrect and surrogate keys make it easier to make design changes in production. Once again, though, this is an *implementation* issue and not a *logic* issue, as I asserted ... -- -Josh BerkusAglio Database SolutionsSan Francisco
Karsten, > Because there is no information to be had on this fact. The > patient IS afflicted by such or she is not. There is no why. I begin to see why I spent $2000 this spring to have a doctor tell me what I *didn't* have ... > http://www.hherb.com/gnumed/schema/ > > Lot's of it isn't in the state yet where we want it but we are > getting there - or so I think. When I have time, sure! But, this afternoon I am off to OSCON so I won't have a chance for 2 weeks at least. Drop me a personal e-mail in August so I don't forget. -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh, > > In other words, with surrogate keys, you eliminate the chance > > that your original design was flawed due to lack of important > > initial knowledge. > > Well, you don't *eliminate* it, but you do decrease it. > > I'd say, yes, this is an important 4th reason: > > 4) Your spec may be incorrect and surrogate keys make it easier to make design > changes in production. thanks so much - this is exactly the kind of assurance I was looking for. BTW, I will come back to your generous offer in August. Thanks again. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Just for the record, the GnuMed schema docs are done nightly with PostgreSQL Autodoc in HTML mode: http://www.rbt.ca/autodoc/ Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jul 23, 2004 at 10:07:48AM -0400, Tom Lane wrote: > The other standard reason for using a made-up value as primary key is > that it's under your control and you can guarantee it isn't going to > change: one record will have the same primary key for its entire life, > which vastly simplifies handling of foreign keys and so forth. That is pretty much the main reason why our schema does so. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Josh Berkus wrote:
> Given: Surrogate keys, by definition, represent no real data;
> Given: Only items which represent real data have any place in
> a data model
> Conclusion: Surrogate keys have no place in the data model
But, once a surrogate key is assigned to a row, doesn't it become a
"real" data? For example, I have a bunch of invoices/receipts and I
write down a unique number on each of them. Doesn't the unique number
become part of the information contained by the invoice/receipt itself
(at least as long as I'm concerned)? Another example is when Adam
(+Eve?) named each animal in the Genesis. At that time the name he gave
for each animal was arbitrary ("surrogate"), but once assigned to each
species, it becomes part of the real data.
> 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and
Change management IMO is perhaps the main reason of surrogate/artificial
key. We often need a PK that _never_ needs to change (because it can be
a royal PITA or downright impossibility to make this change; the PK
might already be printed on a form/card/document, recorded on some
permanent database, tattoed/embedded in someone's forehead, etc).
Meanwhile, every other aspect of the data can change (e.g. a person can
change his name, sex, age, email, address, even date & place of birth).
Not to mention data entry mistakes. So it's impossible to use any
"real"/natural key in this case.
> Now, you're probably wondering "why does this guy regard surrogate keys as a
> problem?" I'll tell you: I absolutely cannot count the number of "bad
> databases" I've encountered which contained tables with a surrogate key, and
> NO REAL KEY of any kind. This makes data normalization impossible, and
> cleanup of the database becomes a labor-intensive process requiring
> hand-examination of each row.
Okay, so surrogate key makes it easy for stupid people to design a
database that is prone to data duplication (because he doesn't install
enough unique constraints to prevent this). But I don't see how a
relation with a surrogate key is harder to "normalize" (that is, for the
duplicates to be removed) than a relation with no key at all. Compare:
street------Green StreetGreen StreetGreen Street
versus:
id street-- ------2934 Green Street4555 Green Street5708 Green Street
They both contain duplicates and/or ambiguous data. They're both equally
hard to normalize/clean.
--
dave
Hi, > But, once a surrogate key is assigned to a row, doesn't it become a > "real" data? For example, I have a bunch of invoices/receipts and I > write down a unique number on each of them. Doesn't the unique number > become part of the information contained by the invoice/receipt itself > (at least as long as I'm concerned)? I don't think that I'd call an invoice number a surrogate key in the first place. Invoice numbers and their like come from business requirements, they just happen to be highly suitable as PKs so they could be considered a natural key. > > 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes and > > Change management IMO is perhaps the main reason of surrogate/artificial > key. We often need a PK that _never_ needs to change (because it can be > a royal PITA or downright impossibility to make this change; the PK > might already be printed on a form/card/document, recorded on some > permanent database, tattoed/embedded in someone's forehead, etc). > Meanwhile, every other aspect of the data can change (e.g. a person can > change his name, sex, age, email, address, even date & place of birth). > Not to mention data entry mistakes. So it's impossible to use any > "real"/natural key in this case. I've never felt that it's a good idea to be dependent on the DBMS providing cascade functionality - particularly cascading updates to PKs. I don't think I've ever worked on a DB that used such constraints. > Okay, so surrogate key makes it easy for stupid people to design a > database that is prone to data duplication (because he doesn't install > enough unique constraints to prevent this). But I don't see how a > relation with a surrogate key is harder to "normalize" (that is, for the > duplicates to be removed) than a relation with no key at all. Compare: I think that most of us would agree that whatever they are, surrogate keys aren't a substitute for clean data (or quality data). On the whole, I think that there are more important indicators of quality (or lack of it) in your database design than the prevalence (or lack) of numeric ID style keys. Personally, I've grown to appreciate the id approach over the years, but my mind is always open to other ideas. regards iain
> > Because there is no information to be had on this fact. The > > patient IS afflicted by such or she is not. There is no why. > > I begin to see why I spent $2000 this spring to have a doctor tell me what I > *didn't* have ... This reminds me of a project I worked on many years ago, I was pretty much fresh out of university writing a system for a large medical practice - itwas principally for accounting purposes. I made lots of suggestions like Josh's, only to get replies like Karsten's. I the progammer wanted to codify everything so as to enable data analysis (ie linking complaints and diagnosis, etc) but the doctors wern't interested. They just wanted to write free text comments. And the reason for it (as far as I can tell) is the distinction between general practice and reseach (such as epidemiology). So (GPs) are not so much searching for new knowlege in their patients records, as applying the knowlege gained from research (done by researchers) to treat individual patients. Karsten might beg to differ, I don't know, but the "long flaky text" comment triggered some old (and fond) memories ;-) Cheers iain
hello > This reminds me of a project I worked on many years ago, I was pretty much > fresh out of university writing a system for a large medical practice - > itwas principally for accounting purposes. I made lots of suggestions like > Josh's, only to get replies like Karsten's. I the progammer wanted to > codify everything so as to enable data analysis (ie linking complaints and > diagnosis, etc) but the doctors wern't interested. They just wanted to > write free text comments. And the reason for it (as far as I can tell) is > the distinction between general practice and reseach (such as > epidemiology). So (GPs) are not so much searching for new knowlege in their > patients records, as applying the knowlege gained from research (done by > researchers) to treat individual patients. Here the situation quite similar, a customer dictate drives the practice far far from logic. EVERY database i had desined in few years have been turned into a container of textual MEMOs completely unstructured. (because of patches and makeups) USER DO NOT WANT TO TAKE CARE OF THEIR INPUT. i do not know how are you all programming, really hope you are in defferent circumstances. Here a customer itself is a user and itself is a [man who formulate a problem] then every stupid idea to simplify input immediately turns to an urgent official order to do. Sorry. That is my work. Thank you all again for very usefull discussion on Surrogate Keys.
> This reminds me of a project I worked on many years ago, I was pretty much > fresh out of university writing a system for a large medical practice - > itwas principally for accounting purposes. I made lots of suggestions like > Josh's, only to get replies like Karsten's. I the progammer wanted to codify > everything so as to enable data analysis (ie linking complaints and > diagnosis, etc) but the doctors wern't interested. Likely it wasn't their intent with the system. That doesn't mean it can't be done ... (not that I think that with todays tools it sufficiently can but ... :-> ICPC probably comes closest to that). Anyway, I am highly interested in increasing the data quality in my records. However, to be able to care for a patient *when needed* I won't allow software I write to *force* coding upon me. Trust me I have and am still researching coding, classifying, structuring of medical data at an ongoing basis which funnily constantly improves my daily abilities as a doctor. Now, back to GnuMed, we *do* allow to code arbitrary pieces of narrative with arbitrarily many codes from arbitrarily many coding systems. Same for classifying (rather typing) data. > They just wanted to write free text comments. And the reason for > it (as far as I can tell) is the distinction between > general practice and reseach (such as epidemiology). Rather it is the difference between reality and theory. In reality you are dealing with tens of patients with 1-5 problems almost neither of which are "quite right" if you go by the textbooks. Nevertheless everyone expects you'll never forget/do wrong a thing. > So (GPs) are not so much searching for new knowlege in their patients records, > as applying the knowlege gained from research (done by researchers) to treat > individual patients. One thing we ARE looking for in our records is the ability to find groups of patients by arbitrary criteria since one day I'll have to find all my patients whose father took a statine, whose second-born child suffered a bout of neutropenia 2 weeks after birth and who started being on the pill at age 14. Because they'll have a 3fold increased risk of lung embolus. Unless monitored for clotting factors every 6 months. Which I will have to do from now on. Get my point ? :-) > Karsten might beg to differ, I don't know, but the "long flaky text" comment > triggered some old (and fond) memories ;-) I totally understand what you are saying... We do cling to old trusted things. Also, I used a bit of hyperbole to get my point across. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Tuesday 27 July 2004 02:04 am, Karsten Hilbert wrote: > One thing we ARE looking for in our records is the ability to > find groups of patients by arbitrary criteria since one day > I'll have to find all my patients whose father took a statine, > whose second-born child suffered a bout of neutropenia 2 weeks > after birth and who started being on the pill at age 14. > Because they'll have a 3fold increased risk of lung embolus. > Unless monitored for clotting factors every 6 months. Which I > will have to do from now on. Get my point ? :-) couldnt you do something like, let them write the 'long flaky text', and at the same time mark a certain number of key words or key phrases which could be stored and retrieved? -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org
В Птн, 23.07.2004, в 09:57, Kenneth Gonsalves пишет: > also, how did you get that neatly formatted output of the schema? This is postgresql_autodoc: http://www.rbt.ca/autodoc/ -- Markus Bertheau <twanger@bluetwanger.de>
> > One thing we ARE looking for in our records is the ability to > > find groups of patients by arbitrary criteria since one day > > I'll have to find all my patients whose father took a statine, > > whose second-born child suffered a bout of neutropenia 2 weeks > > after birth and who started being on the pill at age 14. > > Because they'll have a 3fold increased risk of lung embolus. > > Unless monitored for clotting factors every 6 months. Which I > > will have to do from now on. Get my point ? :-) > > couldnt you do something like, let them write the 'long flaky text', and at > the same time mark a certain number of key words or key phrases which could > be stored and retrieved? I was thinking along similar lines. On one hand, you need the "long flaky text" (love that expression), on the other, you want to ensure that you can locate appropriate data, and that the required details are available. By available, I mean that it was entered in the first place, and that it is retrievable. I imagine a system whereby you define keywords and attributes for them (attributes would be an episode date, or dosage, etc). The memo, is checked for keywords and the doctor prompted to supply the attributes for them. If your parsing was smart, and the memo formated a little, you could conceivably pull a lot of this out of the memo as defaults. The processing could also be done retrospectively by an intern or researcher, but I imagine it would be best to have the doctor do it at the time. Just some vague ideas anyway. This may of course be much more work than anyone wants to get into... I don't have much experience with text searching systems, but something reasonably sophisticated would probably get you there. Regards Iain
On Wednesday 28 July 2004 06:59 am, Iain wrote: > > couldnt you do something like, let them write the 'long flaky text', and > > at > > > the same time mark a certain number of key words or key phrases which > > could > > > be stored and retrieved? > > I was thinking along similar lines. On one hand, you need the "long flaky > text" (love that expression), on the other, you want to ensure that you can > locate appropriate data, and that the required details are available. By > available, I mean that it was entered in the first place, and that it is > retrievable. I imagine a system whereby you define keywords and attributes > for them (attributes would be an episode date, or dosage, etc). The memo, > is checked for keywords and the doctor prompted to supply the attributes > for them. If your parsing was smart, and the memo formated a little, you > could conceivably pull a lot of this out of the memo as defaults. The > processing could also be done retrospectively by an intern or researcher, > but I imagine it would be best to have the doctor do it at the time. simpler, as a first stage and easily implemented, give him some way he can tag words and phrases he feels important. save these in a table along with a foreign key identifying the source. as a second stage keep analysing the words and phrases chosen and empirically build up a database of significant words and phrases relevant to that specific installation (or doctor), and as a third stage, highlight these as he types in the data -- regards kg http://www.onlineindianhotels.net - hotel bookings reservations in over 4600 hotels in India http://www.ootygolfclub.org
> retrievable. I imagine a system whereby you define keywords and attributes > for them (attributes would be an episode date, or dosage, etc). This is pretty much exactly what we are working on. We are factoring out data into dedicated tables where that is possible due to the structured nature of the data and store the remaining narrative as TEXT with associated attributes of all kinds. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
> simpler, as a first stage and easily implemented, give him some way he can > tag words and phrases he feels important. save these in a table along with a > foreign key identifying the source. as a second stage keep analysing the > words and phrases chosen and empirically build up a database of significant > words and phrases relevant to that specific installation (or doctor), and as > a third stage, highlight these as he types in the data Even simpler. Most notes only ever contain those words that are relevant :-) So what we are doing is matching different parts of the progress note, eg. when the doctor types "cough, congested nose, sore throat" as a complaint we pull in the drugs he ususally prescribes for that condition and the dosages she ususally uses ... Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
David, > But, once a surrogate key is assigned to a row, doesn't it become a > "real" data? For example, I have a bunch of invoices/receipts and I > write down a unique number on each of them. Doesn't the unique number > become part of the information contained by the invoice/receipt itself > (at least as long as I'm concerned)? Sure ... *if* it's being used that way. If, however, your table has that Invoice # *and* a seperate surrogate key that's redundant and can cause problems. > Change management IMO is perhaps the main reason of surrogate/artificial > key. We often need a PK that _never_ needs to change (because it can be > a royal PITA or downright impossibility to make this change; the PK > might already be printed on a form/card/document, recorded on some > permanent database, tattoed/embedded in someone's forehead, etc). Sure. But surrogate keys don't fix this problem; only good change management does. This is precisely why I say "use with caution"; all too often project leaders regard surrogate keys as a substitute for good change management and don't do any further work. > Meanwhile, every other aspect of the data can change (e.g. a person can > change his name, sex, age, email, address, even date & place of birth). > Not to mention data entry mistakes. So it's impossible to use any > "real"/natural key in this case. Absolutely false. It's quite possible, it's just a performance/schema/data management issue. This also applies to my comment above. > Okay, so surrogate key makes it easy for stupid people to design a > database that is prone to data duplication (because he doesn't install > enough unique constraints to prevent this). But I don't see how a > relation with a surrogate key is harder to "normalize" (that is, for the > duplicates to be removed) than a relation with no key at all. Compare: You're right here, both are equally hard to normalize. What I'm criticizing is the tendency of a lot of beginning DBAs -- and even some books on database design -- to say: "If you've created an integer key, you're done." Had I my way, I would automatically issue a WARNING on any time you create a table in PG without a key. -- -Josh BerkusAglio Database SolutionsSan Francisco
On Saturday 07 August 2004 04:12 am, Josh Berkus wrote: > > > Meanwhile, every other aspect of the data can change (e.g. a person can > > change his name, sex, age, email, address, even date & place of birth). > > Not to mention data entry mistakes. So it's impossible to use any > > "real"/natural key in this case. > > Absolutely false. It's quite possible, it's just a > performance/schema/data management issue. This also applies to my comment > above. why shouldnt the primary key change? the only key that should never change is a key that is used as a foreign key in another table. In a table like this: id serial unique name varchar primary key name may change - id will never change. id is used as the foreign key -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org
On Sat, 2004-08-07 at 05:00, Kenneth Gonsalves wrote: > why shouldnt the primary key change? the only key that should never change is > a key that is used as a foreign key in another table. In a table like this: > > id serial unique > name varchar primary key > > name may change - id will never change. id is used as the foreign key It can change if you use ON UPDATE CASCADE in the foreign key definitions. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "Be still before the LORD and wait patiently for him; do not fret when mensucceed in their ways, when they carry out their wicked schemes." Psalms 37:7
On Sunday 08 August 2004 06:36 am, Oliver Elphick wrote: > On Sat, 2004-08-07 at 05:00, Kenneth Gonsalves wrote: > > why shouldnt the primary key change? the only key that should never > > change is a key that is used as a foreign key in another table. In a > > table like this: > > > > id serial unique > > name varchar primary key > > > > name may change - id will never change. id is used as the foreign key > > It can change if you use ON UPDATE CASCADE in the foreign key > definitions. but why would anyone want to change the value of an autogenerated serial row? -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org
Kenneth, > but why would anyone want to change the value of an autogenerated serial row? But if you're using a real key, it may need to change. The only reason *not* do do it that way is performance issues with CASCADE. -- -Josh BerkusAglio Database SolutionsSan Francisco
On Sunday 08 August 2004 04:29, Kenneth Gonsalves wrote: > but why would anyone want to change the value of an autogenerated serial > row? because of catenating data from two or more databases.