RE: RE: Primary Key Justification
My apologies. I should have said Business Basic. We were taught bad database design skills. Luckily for me, I had other RDBMS experience before I started. I did have many problems with RPG programmers who designed poor tables in a previous job. Russel Madere Webmaster 504.832.9835 SunShine Pages by EATEL www.sunshinepages.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 9:00 AM To: CF-Talk Subject: Re: RE: Primary Key Justification I'm not sure I completely agree with that statement about RPG programmers - I began my career doing Relational Database design and programming for OS400 using RPG. Their design and development (if done right) should be completely sound, maybe more so than what's produced by others. One thing you've got to keep in mind is that the old school RPG developers dealt with machines that had serious memory limitations... and so OS400 also had these limitations. For example, variable names couldn't exceed 6 characters... allowing more characters meant using up extra memory. The same line of thought applies to table, procedure, and column names as well. That said, anyone with a lot of experience in RPG needs to do a little reading and get with the times if they want to do database design using modern hardware and/or modern software... and I'd encourage them to do so. Developers that have never worked within the limitations that older systems used to apply don't really appreciate how easy they've got it ;) ~Simon Since I have a little experience with older (as in early 90s) AS400 and Business Basic development, I can attest to the fact that the tables are done differently. I have had to use an employee table that required a primary key based upon 3 fields out of nine. I defined a new primary key for my relational work. Older (see above) AS400 and RPG developers do not make good relational database designers in general. The relational model sometimes just is beyond their comprehension. Remind this developer that a web application has less horsepower than a comparable AS400 application. Because of that the database needs a single field primary key. Russel Madere Webmaster 504.832.9835 SunShine Pages by EATEL www.sunshinepages.com -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 8:01 AM To: CF-Talk Subject: Primary Key Justification I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183851 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: RE: Primary Key Justification
My apologies. I should have said Business Basic. We were taught bad database design skills. Luckily for me, I had other RDBMS experience before I started. I did have many problems with RPG programmers who designed poor tables in a previous job. Russel Madere Webmaster 504.832.9835 SunShine Pages by EATEL www.sunshinepages.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 9:00 AM To: CF-Talk Subject: Re: RE: Primary Key Justification I'm not sure I completely agree with that statement about RPG programmers - I began my career doing Relational Database design and programming for OS400 using RPG. Their design and development (if done right) should be completely sound, maybe more so than what's produced by others. One thing you've got to keep in mind is that the old school RPG developers dealt with machines that had serious memory limitations... and so OS400 also had these limitations. For example, variable names couldn't exceed 6 characters... allowing more characters meant using up extra memory. The same line of thought applies to table, procedure, and column names as well. That said, anyone with a lot of experience in RPG needs to do a little reading and get with the times if they want to do database design using modern hardware and/or modern software... and I'd encourage them to do so. Developers that have never worked within the limitations that older systems used to apply don't really appreciate how easy they've got it ;) ~Simon [snip] ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183867 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Ask this person how they plan to remove duplicates, should they be accidentally created. For that matter, ask how they plan to detect duplicates. If that doesn't work, insist that you be referred to by your full name, date of birth, and social security number. Don't answer to anything else. Seriously? Come up with a couple of scenarios where they're indespensible (like the duplicates) and lay them out. If that doesn't work, scream and pull your hair out. --Ben Dawson, Michael wrote: I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183710 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Primary Key Justification
Personally I use an individual primary key myself. In the past I have seen oracle databases with two fields as the primary key, but never a whole record. The only justification I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. On modern servers storage is not usually a problem, particularly with such a small field. On older mainframes, it was. One major justification for a single primary key is replication. If you have multiple fields as a primary key, on a replicated system, there is a higher chance that the same values will appear. It will at least cause more headaches and more setting up initially. Gavin -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: 09 November 2004 14:01 To: CF-Talk Subject: Primary Key Justification I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183712 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Primary Key Justification
From: Gavin Brook [mailto:[EMAIL PROTECTED] Personally I use an individual primary key myself. In the past I have seen oracle databases with two fields as the primary key, but never a whole record. The only justification I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. On modern servers storage is not usually a problem, particularly with such a small field. On older mainframes, it was. I can not speak to AS400 as that was the technology in text books 10 years prior my college experience (yes I'm a youngin'). Anyway, any space you might save will be lost when you do updates or selects in which you want a specific record assuming there is a comparable stored procedure concept on AS400. Consider the extra lines needed to determine which record to update or select: UPDATE SET WHERE a=1, b=2, c=3, e=4, f=5 etc etc etc. SELECT FROM .. WHERE a=1, b=2, c=3, e=4, f=5 etc etc etc. Compared to UPDATE SET WHERE a=1 SELECT FROM .. WHERE a=1 That's not to say that there is never a need for compound keys comprised of 2 or 3 columns such as those found in join tables, etc... ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183715 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
The primary key should flow from the question: What uniquely defines a record? Compound primary keys are fairly common (at least in my company's ugly Informix tables). I don't really see a scenario where the entire table represents the primary key though. Primary keys uniquely identify some piece of dataif the entire table is the key, what piece of data is being uniquely identified From: Gavin Brook [mailto:[EMAIL PROTECTED] Personally I use an individual primary key myself. In the past I have seen oracle databases with two fields as the primary key, but never a whole record. The only justification I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. On modern servers storage is not usually a problem, particularly with such a small field. On older mainframes, it was. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183716 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Primary Key Justification
Since I have a little experience with older (as in early 90s) AS400 and Business Basic development, I can attest to the fact that the tables are done differently. I have had to use an employee table that required a primary key based upon 3 fields out of nine. I defined a new primary key for my relational work. Older (see above) AS400 and RPG developers do not make good relational database designers in general. The relational model sometimes just is beyond their comprehension. Remind this developer that a web application has less horsepower than a comparable AS400 application. Because of that the database needs a single field primary key. Russel Madere Webmaster 504.832.9835 SunShine Pages by EATEL www.sunshinepages.com -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 8:01 AM To: CF-Talk Subject: Primary Key Justification I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183717 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Dawson, Michael wrote: I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. That is probably not smart performance wise: - you will have 6-fold foreign keys which will have to cascade on every change to the record - your PK-index will be huge - some of the fields are bound to be non-unique thus reducing the efficiency of an index Additional storage requirements of adding a field are easily offset by the reduced storage requirements of having an index over all six fields. Add in a bit off application developer convenience and your co-worker has to come with better arguments to convince me. Jochem ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183718 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Dawson, Michael wrote: I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. There's no justification for doing that. None! This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) Say it with my, kids: Most. Useless. Table. Indexing. Ever. I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... Moreover, what use is the table when you have to pass around whole tuples to do lookups on it? A few things spring to mind: your cow-orker has no clue what normalisation is; they A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. The thing is, they should know this *anyway*! Take the example of a users table, like this CREATE TABLE users ( user_name VARCHAR(16) NOT NULL PRIMARY KEY, user_password VARCHAR(32) NOT NULL, -- Holds MD5 hash of password -- ...other fields... ); user_name makes sense as a primary key: it's uniques for each tuple, and it's never going to change. What is this person doing designing table schemas anyway? Now, I'm creating the table myself to do it right. ;-) Do, and if they complain then do some benchmarking to show them that your schema is just better. Then tell them that's how it's done. -- Keith Gaughan, Developer Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland http://digital-crew.com/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183719 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
I may be way out there, but I think using the whole row as a primary key is exactly the opposite of a primary key. Primary Key: A key that uniquely identifies each record and is used as the primary method of accessing the records - File Structures ISBN 0-201-87401-6 So, unless the intention is to give every single field every time you want any record - the suggestion is crazy talk - and a very bad hashing algorithm at that :). Tell'em to look here: http://www.google.com/search?hl=enq=Primary+Key+best+practice Cheers rob On Tue, 9 Nov 2004 08:01:18 -0600, Dawson, Michael [EMAIL PROTECTED] wrote: I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183720 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Gavin Brook wrote:
Personally I use an individual primary key myself. In the past I have seen
oracle databases with two fields as the primary key, but never a whole
record.
In a many-to-many relation I often to have something like:
CREATE TABEL author_article {
authorID INT NOT NULL,
articleID INT NOT NULL,
CONSTRAINT author_article_pk PRIMARY KEY (authorID, articleID),
CONSTRAINT author_fk FOREIGN KEY (authorID) REFERENCES author,
CONSTRAINT article_fk FOREIGN KEY (articleID) REFERENCES article
);
One major justification for a single primary key is replication. If you have
multiple fields as a primary key, on a replicated system, there is a higher
chance that the same values will appear.
A combination of fields is unique or not, replication has nothing
to do with that.
Jochem
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183721
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: RE: Primary Key Justification
I'm not sure I completely agree with that statement about RPG programmers - I began my career doing Relational Database design and programming for OS400 using RPG. Their design and development (if done right) should be completely sound, maybe more so than what's produced by others. One thing you've got to keep in mind is that the old school RPG developers dealt with machines that had serious memory limitations... and so OS400 also had these limitations. For example, variable names couldn't exceed 6 characters... allowing more characters meant using up extra memory. The same line of thought applies to table, procedure, and column names as well. That said, anyone with a lot of experience in RPG needs to do a little reading and get with the times if they want to do database design using modern hardware and/or modern software... and I'd encourage them to do so. Developers that have never worked within the limitations that older systems used to apply don't really appreciate how easy they've got it ;) ~Simon Since I have a little experience with older (as in early 90s) AS400 and Business Basic development, I can attest to the fact that the tables are done differently. I have had to use an employee table that required a primary key based upon 3 fields out of nine. I defined a new primary key for my relational work. Older (see above) AS400 and RPG developers do not make good relational database designers in general. The relational model sometimes just is beyond their comprehension. Remind this developer that a web application has less horsepower than a comparable AS400 application. Because of that the database needs a single field primary key. Russel Madere Webmaster 504.832.9835 SunShine Pages by EATEL www.sunshinepages.com -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 8:01 AM To: CF-Talk Subject: Primary Key Justification I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183722 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
This sounds familiar - strictly speaking, 3rd normal form databases are typically sufficiently normalised for everyday use. I do not remember all the terminology (dependencies, etc), but 4th/5th/Boyce-Codd (spelling?) normal form would lead to an entity/table design not unlike the one you are describing - each tuple/record can only be described uniquely by the combination of 1 or more fields and no other way. This theory is something we learnt at university, and imo, for 99% of cases in the real world, the theory should be left there. The co-worker is designing a fully normalised database table, but this does not mean it is programmer / web / etc friendly. It is quite possible a throw-back to AS400 / IBM development mentality where things are done the right way. The AS400 hardware is a case in point, they are as old as the hills and still going strong. I have no experience in AS400 development so this is pure conjecture. Neither of you are wrong per se. You have an opportunity to learn elite db design and configuration. In return you can teach your AS400 co-worker about web development and some nifty short-cuts (identity/sequences) that may make his life at work a little easier. Just some thoughts. HTH Aaron - Original Message - From: Dawson, Michael [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 1:01 AM Subject: Primary Key Justification I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183723 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Overhead. By defining every column a a primary key, you are basically creating an index of the entire table. So any column that gets updated, forces the index to update. Hence making the indexes pretty worthless. -Adam On Tue, 9 Nov 2004 09:42:31 -0500, Tangorre, Michael [EMAIL PROTECTED] wrote: From: Gavin Brook [mailto:[EMAIL PROTECTED] Personally I use an individual primary key myself. In the past I have seen oracle databases with two fields as the primary key, but never a whole record. The only justification I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. On modern servers storage is not usually a problem, particularly with such a small field. On older mainframes, it was. I can not speak to AS400 as that was the technology in text books 10 years prior my college experience (yes I'm a youngin'). Anyway, any space you might save will be lost when you do updates or selects in which you want a specific record assuming there is a comparable stored procedure concept on AS400. Consider the extra lines needed to determine which record to update or select: UPDATE SET WHERE a=1, b=2, c=3, e=4, f=5 etc etc etc. SELECT FROM .. WHERE a=1, b=2, c=3, e=4, f=5 etc etc etc. Compared to UPDATE SET WHERE a=1 SELECT FROM .. WHERE a=1 That's not to say that there is never a need for compound keys comprised of 2 or 3 columns such as those found in join tables, etc... ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183728 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. Not even, you may save the space of an integer key in the table, but think about the index! ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183731 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
This is how it's done, is the reply I get. Tell them the Titanic sunk because of the way it was done! ;-) -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183732 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Primary Key Justification
Michael: I see three basic problems with this assuming that other tables need to be linked to this table: 1. Size - Since the entire table needs to be replicated in each table that it is linked to, this is highly inefficient 2. Seek Performance - Searching and comparing long text fields can become prohibitive depending on the size of the tables 3. Update time - if the table is updated frequently, then each of the linked tables need to be updated as well as do their indices If this table is not linked to anything or is rarely changed and if disk space and performance are not issues, then a re-writing is not worth it. But if the table has too be modified, it is probably worth adding a primary key and rolling to each linked table as either performance becomes an issue or other maintenance is performed. Andy -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 8:01 AM To: CF-Talk Subject: Primary Key Justification I'm well aware of the need for a primary key, in addition to a sound table design, however, I'd like someone else to word a response for me. A co-worker built a data file (a data table on an AS400) with six fields. The only unique key of the record is the entire record, itself. The combination of all six fields must be used to identify the exact record. This is how it's done, is the reply I get. (I've been building web-based apps using relational DBs for 8 years. I have never used an entire record as the primary key.) I'm not passing an entire record through a URL or in hidden form fields. Imagine the nightmare of maintaining this application... A discussion of the existence of Oracle's sequences and SQL's identity fields did little to sway this person's opinion. This person's entire development background is AS400 with a history of bad database design. Now, I'm creating the table myself to do it right. ;-) Any thoughts? Thanks M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183734 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Claude Schneegans wrote: I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. Not even, you may save the space of an integer key in the table, but think about the index! But there's no sense if saving space like that when you need a whole tuple to do a lookup. -- Keith Gaughan, Developer Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland http://digital-crew.com/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183753 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Keith Gaughan wrote: Claude Schneegans wrote: I can see for having the whole record as the primary key is to save space. Adding an extra column to store a primary key requires the space to store it and the storage for the sequence. Not even, you may save the space of an integer key in the table, but think about the index! But there's no sense if saving space like that when you need a whole tuple to do a lookup. I think that was the point. -- Registration for MX Europe 2005 is now open. http://www.mxeurope.org/go/registration Early bird discounts available. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183759 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Primary Key Justification
I want to thank everyone for their input. There were many great justifications and some pretty funny remarks! Thanks again! M!ke ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183760 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Primary Key Justification
Stephen Moretti (cfmaster) wrote: Keith Gaughan wrote: But there's no sense if saving space like that when you need a whole tuple to do a lookup. I think that was the point. I know that's the point: I was just agreeing with the bloke. -- Keith Gaughan, Developer Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland http://digital-crew.com/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183765 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
