Re: [GENERAL] primary key and existing unique fields
Dawid, I am interested in the first point you made that: having varchar(12) in every referencing table, takes more storage space. The thing is though, if I have a serial primary key then it would be an additional column. Or you are saying the space taken by a VARCHAR(12) field is more than two INT fields? ( or is it the fact that when it is referenced it will appear several times?) I guess the reason I am resisting the idea of an additional primary key field is to avoid the additional lookup in some queries. Perhaps it's a minor almost irrelevant performance factor. Thanks Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] primary key and existing unique fields
On Thu, Oct 28, 2004 at 14:31:32 +, Sally Sally [EMAIL PROTECTED] wrote: Dawid, I am interested in the first point you made that: having varchar(12) in every referencing table, takes more storage space. The thing is though, if I have a serial primary key then it would be an additional column. Or you are saying the space taken by a VARCHAR(12) field is more than two INT fields? ( or is it the fact that when it is referenced it will appear several times?) I guess the reason I am resisting the idea of an additional primary key field is to avoid the additional lookup in some queries. Perhaps it's a minor almost irrelevant performance factor. I think it is better to worry about what is going to make it easiest to have clean data and to support future changes than worry about performance. Over the long run hardware is cheaper than people. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] primary key and existing unique fields
I think the same too but sometimes it seems in the real world performance is given more value than a properly designed db. Or the long term flexiblity is not taken into account given the short term requirements. regards Sally From: Bruno Wolff III [EMAIL PROTECTED] To: Sally Sally [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [GENERAL] primary key and existing unique fields Date: Thu, 28 Oct 2004 12:44:00 -0500 On Thu, Oct 28, 2004 at 14:31:32 +, Sally Sally [EMAIL PROTECTED] wrote: Dawid, I am interested in the first point you made that: having varchar(12) in every referencing table, takes more storage space. The thing is though, if I have a serial primary key then it would be an additional column. Or you are saying the space taken by a VARCHAR(12) field is more than two INT fields? ( or is it the fact that when it is referenced it will appear several times?) I guess the reason I am resisting the idea of an additional primary key field is to avoid the additional lookup in some queries. Perhaps it's a minor almost irrelevant performance factor. I think it is better to worry about what is going to make it easiest to have clean data and to support future changes than worry about performance. Over the long run hardware is cheaper than people. ---(end of broadcast)--- TIP 8: explain analyze is your friend _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] primary key and existing unique fields
-Original Message- From: Robby Russell [mailto:[EMAIL PROTECTED] Sent: Tue 10/26/2004 9:08 PM To: Kevin Barnard Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] primary key and existing unique fields On Tue, 2004-10-26 at 22:03 -0500, Kevin Barnard wrote: On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell [EMAIL PROTECTED] wrote: On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote: joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking m.. string theory. :-) Ya you know the theory that states that the Database is really made up of a large amount of strings. Some are even null terminated strings, although most strings really have a quanta that can be found immediate before the string. :-) How do we SELECT the string so that we can observe it then? ;-) -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development / You can't observe it ... only *infer* it. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] primary key and existing unique fields
Sally Sally wrote: This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. Plenty of people are contributing their tuppence-worth regarding the choice of surrogate vs natural primary key. Can I just point out that your existing unique field is EITHER a numeric value OR it has a fixed number of characters - numbers don't have leading zeros. If what you have is a number, then perhaps consider int8/numeric types and format appropriately when you display the values. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] primary key and existing unique fields
On Wed, Oct 27, 2004 at 00:10:27 +0200, Dawid Kuroczko [EMAIL PROTECTED] wrote: 3. If you'll need things like last 50 keys, you can SELECT * FROM foo ORDER BY yourserialkey DESC LIMIT 50; You really shouldn't be doing that if you are using sequences to generate the key. Sequences are just guarenteed to return unique values, not to return them in order. Because groups of sequences can be allocated to a backend at once depending on a setting settable by a client, you can get assignments out of order. Also for overlapping transactions what the application means by the last 50 entries may not match what you get when you get the 50 highest serial values. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] primary key and existing unique fields
Title: RE: [GENERAL] primary key and existing unique fields Since you already have the unique field I see no point in adding a sequence to the table, unless of course the sequence of the data inserts is of importance at some point. Duane -Original Message- From: Sally Sally [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 26, 2004 9:25 AM To: [EMAIL PROTECTED] Subject: [GENERAL] primary key and existing unique fields Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for adding a separate autoincrement primary key field for each table? or either way is not a big deal. Sally _ Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] primary key and existing unique fields
Sally Sally wrote: Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for adding a separate autoincrement primary key field for each table? or either way is not a big deal. Your primary key should not be directly related to the data being stored. Outside of the fact that it is the primary reference or the row. Sincerely, Joshua D. Drake Sally _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] primary key and existing unique fields
Can you please elaborate on the point you just made as to why the primary key should not relate to the data (even for a case when there is an existing unique field that can be used to identify the record) From: Joshua D. Drake [EMAIL PROTECTED] To: Sally Sally [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] primary key and existing unique fields Date: Tue, 26 Oct 2004 09:48:50 -0700 Sally Sally wrote: Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for adding a separate autoincrement primary key field for each table? or either way is not a big deal. Your primary key should not be directly related to the data being stored. Outside of the fact that it is the primary reference or the row. Sincerely, Joshua D. Drake Sally _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL jd.vcf ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] primary key and existing unique fields
Sally Sally wrote: Can you please elaborate on the point you just made as to why the primary key should not relate to the data (even for a case when there is an existing unique field that can be used to identify the record) Here is a good article on the topic: http://www.devx.com/ibm/Article/20702 Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] primary key and existing unique fields
Joshua D. Drake wrote: Sally Sally wrote: Can you please elaborate on the point you just made as to why the primary key should not relate to the data (even for a case when there is an existing unique field that can be used to identify the record) Here is a good article on the topic: http://www.devx.com/ibm/Article/20702 That article makes me want to vomit uncontrollably! ;-) Business data might also simply be bad -- glitches in the Social Security Administration's system may lead to different persons having the same Social Security Number. A surrogate key helps to isolate the system from such problems. The surrogate key isn't solving the underlying logical inconsistency problem. It is being used as a work-around to cover one up. I suspect the author of being a MySQL user. Mike Mascari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] primary key and existing unique fields
Here is a good article on the topic: http://www.devx.com/ibm/Article/20702 The surrogate key isn't solving the underlying logical inconsistency problem. It is being used as a work-around to cover one up. I suspect the author of being a MySQL user. Actually he is a software project consultant for IEEE.org and he holds a Ph.D. in Theoretical Physics. Sincerely, Joshua D. Drake Mike Mascari ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] primary key and existing unique fields
Joshua D. Drake wrote: Here is a good article on the topic: http://www.devx.com/ibm/Article/20702 The surrogate key isn't solving the underlying logical inconsistency problem. It is being used as a work-around to cover one up. I suspect the author of being a MySQL user. Actually he is a software project consultant for IEEE.org and he holds a Ph.D. in Theoretical Physics. joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking Seriously, my only point was that Date Darwen and other relational purists do not use surrogate keys. The surrogate-key vs. speaking-key debate devolves quickly. I'd only argue that it is wrong as the author of the article implied that the speaking-key side of the debate is without merit. He begins: For the purpose of data modeling, the plumbing should be largely transparent. In fact, purist DB lore makes no distinction between data and plumbing. However, you will see that it is more efficient for administration and maintenance, as well as in terms of runtime performance, to have some additional fields to serve as DB keys. So he dismisses the speaking-key argument in one sentence as purist DB lore. He then proceeds with a poor example: The requirements for a primary key are very strict. It must: Exist Be unique Not change over time Surrogate keys help to mitigate the fact that real business data never reliably fulfills these requirements. Not every person has a Social Security Number (think of those outside the U.S.), people change their names, and other important information. 1. The reason we have ON UPDATE CASCADE is to handle changes in primary keys. 2. If not everyone has a social security number than the design should be sufficiently normalized to reflect that fact. I am not saying that Chris Date and Hugh Darwen are right and that Philipp Janert is wrong. I am only saying that both sides should be investigated and judged on the weight of their arguments. Personally, I've found over time that when I deviate from purist DB lore I get punished in long run. Could be wrong, though. :-) Mike Mascari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] primary key and existing unique fields
On Tue, 26 Oct 2004 16:24:44 +, Sally Sally [EMAIL PROTECTED] wrote: Hi all, I am wandering about the pros and cons of creating a separate serial field for a primary key when I already have a single unique field. This existing unique field will have to be a character of fixed length (VARCHAR(12)) because although it's a numeric value there will be leading zeroes. There are a couple more tables with similar unique fields and one of them would need to reference the others. Does anybody see any good reason for adding a separate autoincrement primary key field for each table? or either way is not a big deal. I see three possible advantages: 1. having varchar(12) in every referencing table, takes more storage space, which may mean something if you have tons of gigabytes of rows. ;) 2. if any of your varchar(12) row's data is likely to change in future, you'll make update of one table, not an update which will CASCADE over many tables. 3. If you'll need things like last 50 keys, you can SELECT * FROM foo ORDER BY yourserialkey DESC LIMIT 50; And a disadvantage: if you'll need to access the data by your varchar(12) key, you'll need to perform JOIN on two tables. If you used varchar(12) as your key, you don't. :) Regards, Dawid ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] primary key and existing unique fields
Title: RE: [GENERAL] primary key and existing unique fields Look at the database design in terms of data retrieval. If I add a sequence number as my primary key, when I get ready to retrieve that record directly how do I know what that sequence number is. For instance, my employee number is 123456789, and it is unique within my company and my sequence number is 375. I will more likely know the employee number to query than I will the sequence number. Sure saves time in data access. I know, you can always create a unique index on the employee number as well as a primary index on the sequence number but WHY would I want to take up room for a field in the record as well as the useless index space for no purpose. Codd said the key, the whole key and nothing but the key. In database design we cannot always do this for query performance but why add something to a record that will serve no real purpose. Keys are updateable, hopefully they are not changed all that often but the ability should be there. If they are not updateable then the database engine is not one I would choose for my application. Duane -Original Message- From: Mike Mascari [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 26, 2004 2:26 PM To: Joshua D. Drake Cc: Sally Sally; [EMAIL PROTECTED] Subject: Re: [GENERAL] primary key and existing unique fields Joshua D. Drake wrote: Here is a good article on the topic: http://www.devx.com/ibm/Article/20702 The surrogate key isn't solving the underlying logical inconsistency problem. It is being used as a work-around to cover one up. I suspect the author of being a MySQL user. Actually he is a software project consultant for IEEE.org and he holds a Ph.D. in Theoretical Physics. joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking Seriously, my only point was that Date Darwen and other relational purists do not use surrogate keys. The surrogate-key vs. speaking-key debate devolves quickly. I'd only argue that it is wrong as the author of the article implied that the speaking-key side of the debate is without merit. He begins: For the purpose of data modeling, the plumbing should be largely transparent. In fact, purist DB lore makes no distinction between data and plumbing. However, you will see that it is more efficient for administration and maintenance, as well as in terms of runtime performance, to have some additional fields to serve as DB keys. So he dismisses the speaking-key argument in one sentence as purist DB lore. He then proceeds with a poor example: The requirements for a primary key are very strict. It must: Exist Be unique Not change over time Surrogate keys help to mitigate the fact that real business data never reliably fulfills these requirements. Not every person has a Social Security Number (think of those outside the U.S.), people change their names, and other important information. 1. The reason we have ON UPDATE CASCADE is to handle changes in primary keys. 2. If not everyone has a social security number than the design should be sufficiently normalized to reflect that fact. I am not saying that Chris Date and Hugh Darwen are right and that Philipp Janert is wrong. I am only saying that both sides should be investigated and judged on the weight of their arguments. Personally, I've found over time that when I deviate from purist DB lore I get punished in long run. Could be wrong, though. :-) Mike Mascari ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] primary key and existing unique fields
That article makes me want to vomit uncontrollably! ;-) Business data might also simply be bad -- glitches in the Social Security Administration's system may lead to different persons having the same Social Security Number. A surrogate key helps to isolate the system from such problems. The surrogate key isn't solving the underlying logical inconsistency problem. It is being used as a work-around to cover one up. I suspect the author of being a MySQL user. I think what he's saying is that an application bug, or a business process problem, should not interfere with your database system. Granted, two identical SSNs seems far fetched. However, if your business screws up and you need to change someone's primary key, you've just violated the principle of a primary key. You better be REALLY sure the primary key will NEVER change over time for a given record, and that it really is unique. An SSN might fit that description, but there are always strange situations. What if someone sues to have their SSN changed and a judge orders it? If that's their PK, the social security administration is up a creek (at least in the DB theory world, it probably wouldn't matter much in practice). Regards, Jeff Davis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] primary key and existing unique fields
On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote: joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking m.. string theory. :-) -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development / signature.asc Description: This is a digitally signed message part
Re: [GENERAL] primary key and existing unique fields
On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell [EMAIL PROTECTED] wrote: On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote: joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking m.. string theory. :-) Ya you know the theory that states that the Database is really made up of a large amount of strings. Some are even null terminated strings, although most strings really have a quanta that can be found immediate before the string. :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] primary key and existing unique fields
On Tue, 2004-10-26 at 22:03 -0500, Kevin Barnard wrote: On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell [EMAIL PROTECTED] wrote: On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote: joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking m.. string theory. :-) Ya you know the theory that states that the Database is really made up of a large amount of strings. Some are even null terminated strings, although most strings really have a quanta that can be found immediate before the string. :-) How do we SELECT the string so that we can observe it then? ;-) -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development / signature.asc Description: This is a digitally signed message part
