If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#41
|
|||
|
|||
BruceM wrote: I am not going to use a multi-field PK. Why? You seem to be basing this on 'gut feeling'. Remember PK = clustered index (physical order), and shouldn't be confused with a simply NOT NULL UNIQUE candidate key. A name and address combination is a poor choice for PK. Remember PK = clustered index. If you have relevant queries which use these columns then they stand a good chance of making an excellent PK. Updating many records if somebody's address changes makes no sense whatever. It destroys the whole point of using a relational database. No, it tells you that using an name + address doesn't make a very good key (as you said earlier) but if it's all that you've got then you are stuck with it (or change the system). You seem to be assuming a key cannot change, again based on gut feeling alone. I ask again, why do you think ON UPDATE CASCADE was invented? |
#42
|
|||
|
|||
John Vinson wrote:
For a Contacts table, last_name, first_name and postal_address makes a fine natural key Fred Brown, xxx D Street, Parma, Idaho Fred Brown, xxx D Street, Parma, Idaho How on earth do you tell them apart!? You'd better issue your friends with ID numbers and start calling them "Fred Brown 1" and "Fred Brown 2". Or do you have some sort of advanced biometrics testing or visual recognition system going on there? Do they ever try and pretend to be the other one, and really mess up the data you are collecting about them? This week, I have spoken to two organizations - supermarket home delivery service and bank respectively - on the phone without my customer reference to hand and they used my 'name' + 'address' + 'has an account with us' as a key i.e. a way of identifying me. Thankfully, the latter had some additional security information I had to supply but the former were just happy to get a truck full of melting ice cream off their hands. |
#43
|
|||
|
|||
BruceM wrote: I am inclined to use autonumber or some other fixed value (e.g. Invoice Number) as the PK. Word of warning: an incrementing autonumber can leave small gaps in a sequence e.g. a autonumber value was served out but the transaction was rolled back and the served number is never reused. I've heard said that if exposed, e.g. as an invoice number, you may have to account for the missing numbers to an auditor. I know from a previous career that an auditor will ask about missing numbers in a sequence of cheque/check numbers. The only 'orders' database application I've worked on was 'life critical', hence we used no autonumbers and all data had to be retained for audit trail purposes, even on rollback. |
#44
|
|||
|
|||
I use some sort of Dmax +1 code to assign the number when I need sequential
numbering. I had intended by the use of "or" to convey that invoice number is not autonumber. wrote in message oups.com... BruceM wrote: I am inclined to use autonumber or some other fixed value (e.g. Invoice Number) as the PK. Word of warning: an incrementing autonumber can leave small gaps in a sequence e.g. a autonumber value was served out but the transaction was rolled back and the served number is never reused. I've heard said that if exposed, e.g. as an invoice number, you may have to account for the missing numbers to an auditor. I know from a previous career that an auditor will ask about missing numbers in a sequence of cheque/check numbers. The only 'orders' database application I've worked on was 'life critical', hence we used no autonumbers and all data had to be retained for audit trail purposes, even on rollback. |
#45
|
|||
|
|||
BruceM wrote: I had intended by the use of "or" to convey that invoice number is not autonumber. I totally read that wrong ;-) |
#46
|
|||
|
|||
To clarify, I am not going to use a multi-field PK when there are other (and
simpler) means to guarantee uniqueness. If I have PartNumber and Process (plating, welding, etc.) it makes sense to combine the two, since the combination of Blade and Plating should appear just once. Combining FirstName, MI, LastName, and a few address fields PLUS an autonumber or some other kind of ID, then storing all of those fields in the Child table, makes no sense to me. I can't believe that it is more efficient storing so much redundant data then to have the index be based on an artificial number. It has a lot to do with reading what others have written here (people who offer a lot pragmatic and practical advice, and who have demonstrated again and again their command of the program). EmployeeID (a four-digit number) works quite well. I have no intention of bloating my database by storing several other (and unnecessary) fields in the interest of a physical order that is as arbitrary as any other way of arranging the data. Sometimes I need to arrange Employee information by criteria other than LastName (by Department, for instance, or by date). Even if I cared about physical order, one way of ordering the records in my table is as good as another when I need to arrange them in so many different ways in the course of using the database. EmployeeID is as good a choice as any. Same with an autonumber. "Destroys" is too strong a word. However, you will not be able to convince me that continually updating multiple records and storing so much redundant data improves anything in a situation where the unique ID number is part of the record anyhow. You will need to look elsewhere for a convert. Your thoughts have been interesting, and have prompted me to investigate some new areas, but are not going to inspire me to rework my basic approach of basing my PK on uniqueness rather than on a particular physical order. wrote in message oups.com... BruceM wrote: I am not going to use a multi-field PK. Why? You seem to be basing this on 'gut feeling'. Remember PK = clustered index (physical order), and shouldn't be confused with a simply NOT NULL UNIQUE candidate key. A name and address combination is a poor choice for PK. Remember PK = clustered index. If you have relevant queries which use these columns then they stand a good chance of making an excellent PK. Updating many records if somebody's address changes makes no sense whatever. It destroys the whole point of using a relational database. No, it tells you that using an name + address doesn't make a very good key (as you said earlier) but if it's all that you've got then you are stuck with it (or change the system). You seem to be assuming a key cannot change, again based on gut feeling alone. I ask again, why do you think ON UPDATE CASCADE was invented? |
#47
|
|||
|
|||
Interesting article. I'll check out the site when I have more time. MSDE
may well be useful at some point, but I do not have the extra time to look into it right now. The reality of my world is that I have a certain number of things I need to accomplish and a limited amount of time in which to do so. SQL server in any form is not on my horizon. wrote in message oups.com... BruceM wrote: I really don't know what you mean by "porting to MSDE". By MSDE I mean SQL Server, the free version. The support for constraints is IMO slightly worse than Jet 4.0 but what MSDE does have that Jet doesn't is triggers. If there isn't a suitable constraint, then a trigger may be employed (a bit like your Before Update, I assume, but at the database level). You can strongly discourage using the front end for such purposes, but have not provided a reason why. Suggested reading: Mop the Floor and Fix the Leak, Part 1, by Joe Celko http://www.dbazine.com/ofinterest/oi-articles/celko25 |
#48
|
|||
|
|||
An ID can be arbitrary, sequential, based on a combination of date and
sequential number or letter and sequential number, etc. Where I work EmployeeID is assigned sequentially, and is not seen except when adding or editing an employee record. If two people have the same name it does not help to distinguish them as John Doe 4421 and John Doe 4241. If with about 100 employee records I confront that kind of duplication I will need to come up with something "real" to distinguish them (MI, Department, Title, or whatever) in the real world. For the purposes of the database, ID number is fine. It is a compact and efficient PK and FK. "Amy Blankenship" wrote in message ... Yes, and StudentID is usually an AutoNumber in my applications, because the way I know which student it is is usually an artificially created but exposed Login ID that will be unique but makes a lousy Foriegn Key to other tables. So I associate that with the actual primary key that's an autonumber. I guess you could do it the other way, associating some arbitrarily assigned Integer with the exposed LoginID and use the integer to make your application development easier (as discussed earlier), but it seems six of one and half dozen of the other... -Amy "BruceM" wrote in message ... Let's suppose I add an arbitrary number to each record. I still can't tell them apart without some other sort of information. What that may be depends on the circumstances. If I am storing course information I will probably use StudentID. Since StudentID is what distinguishes them from each other, I will just use that as the PK. If two people with the same name enter the school the same year, are in the same graduating class, and have the same middle initial, I will need to come up with something else to assure they are not confused with each other in the records. I am not going to use a multi-field PK. Names and addresses change. A name and address combination is a poor choice for PK. Updating many records if somebody's address changes makes no sense whatever. It destroys the whole point of using a relational database. "Amy Blankenship" wrote in message ... And what if you absolutely have to tell Miss NE personID = 1 from Miss NE personID = 2, for exam results and such? For a contact database it may NOT be important who it is, but for other purposes, like storing course completion results, etc., it is VERY important! -Amy "BruceM" wrote in message ... "I do not rely on front end applications to enforce data integrity and would strongly discourage such development. If I have a business rule which Jet cannot enforce via constraints then I would recommend porting to MSDE." I really don't know what you mean by "porting to MSDE". I found out what MSDE is, but for my purposes I will find a way to validate the data, whether at the table level or in the front end. You can strongly discourage using the front end for such purposes, but have not provided a reason why. If there is a Spouse First Name field it may be required if the person is married, but certainly not otherwise. My choice is the front end for such validation rather than another piece of software. Before Update works for my purposes. I will continue to use it. Data integrity is not compromised. The database works smoothly and quickly. If it is "inefficient" it is so on a level that is not important to me. "Miss N E Person ID=1 of Main Street and Miss N E Person ID=2 of Main Street are different people". Then I will figure out a way of telling them apart that is useful to the person who needs to call or contact one or the other. Knowing that they are different records in the database (because they have different ID numbers) is not helpful in telling them apart. wrote in message oups.com... BruceM wrote: I think I understand that your PK and your field(s) on the one side of one-to-many may not be the same. If so, and if the PK is not part of a relationship, would the purpose of your PK be to guard against duplication? No, it would be the 'field(s) on the one side of one-to-many' that would guard against duplication. The purpose of the PK would be to avoid a performance-degrading clustered index and, if possible, to provide for a performance-enhancing clustered index. If so, do you regard that as a more efficient use of recources than data validation code in the form's Before Update event? I do not rely on front end applications to enforce data integrity and would strongly discourage such development. If I have a business rule which Jet cannot enforce via constraints then I would recommend porting to MSDE. For me, 'efficiency' doesn't come anything close to data integrity in terms of importance. Also, if by "expose it" (in reference to an artificial key) you mean show it to the user, why would that be necessary? You know Miss N E Person ID=1 of Main Street and Miss N E Person ID=2 of Main Street are different people, but when you speak to one of them on the phone, how do *they* tell you which one they are? |
#49
|
|||
|
|||
BruceM wrote: To clarify, I am not going to use a multi-field PK when there are other (and simpler) means to guarantee uniqueness. If I have PartNumber and Process (plating, welding, etc.) it makes sense to combine the two, since the combination of Blade and Plating should appear just once. I'll try one last attempt at getting the message across. Use your multi-field PK to build the clustered index. The clustered index is for that table and that table alone. Use your simpler 'PartNumber and Process' in the FOREIGN KEY relationship. If it isn't already, constrain 'PartNumber and Process' with NOT NULL UNIQUE. Remember that you can have many NOT NULL UNIQUE constraints in a table but only one clustered index (=PK) It has a lot to do with reading what others have written here (people who offer a lot pragmatic and practical advice, and who have demonstrated again and again their command of the program) Ask yourself: do these other people recommend an autonumber as PRIMARY KEY in the knowledge that it creates a clustered index (physical ordering)? I've yet to hear anyone here say, 'Yes, autonumber makes for a fine clustered index.' |
#50
|
|||
|
|||
Roger Carlson wrote: data type = INTEGER autonumbers are Long Integer, not Integer Have you heard of the expression, "Win win"? I use the uppercase INTEGER when referring to the Jet data type (see http://office.microsoft.com/en-us/as...322291033.aspx) which coincides nicely with the ANSI data type. It's not about losing. Open you mind and you may learn something. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting a table by concatenating several fields in the same table | salsaguy | Running & Setting Up Queries | 3 | March 6th, 2005 09:41 PM |
Sorting a table by concatenating several fields in the same table | salsaguy | Running & Setting Up Queries | 0 | March 6th, 2005 02:33 AM |
Additional fields for form based parameter query/null fields | geeksdoitbetter | Running & Setting Up Queries | 2 | January 7th, 2005 11:05 PM |
Selecting Fields for Update | Steve Daigler | Page Layout | 4 | October 15th, 2004 02:13 PM |
My tables lost their AutoNumber fields | Bill Nicholson | Database Design | 2 | July 2nd, 2004 02:20 AM |