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 |
#31
|
|||
|
|||
wrote in message oups.com... Roger Carlson wrote: Autonumber fields make excellent Primary Keys. You've misunderstood what PRIMARY KEY means. An unique integer which has no meaning in respect fo the entities being modelled makes a lousy PRIMARY KEY. Google for "clustered index" in the Access groups. An autonumber is a convenient uniqueifier but unquieness for its own sake make not be such a good thing. |
#32
|
|||
|
|||
|
#33
|
|||
|
|||
BruceM wrote:
"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. [snip] The basic argument is that if you enforce rules via your front end then the rules are ONLY enforced in your front end. If someone else created another front end linked to your tables then the rules would not be enforced. Or if you yourself end up creating another front end or form to edit the same tables you have to replicate your "code rules" over and over each time. If the rules are enforced by the database engine then they are written once and are automatically enforced in all situations. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#34
|
|||
|
|||
I see. Thanks for pointing it out. I am in a situation where I am pretty
much the only one working on database design, and my projects have all been on a rather modest scale. I had not considered the problems of somebody else making another front end linked to the existing tables, because it isn't going to happen in the current scheme of things. I can see where it would definitely be worth considering in other situations. I am still not sure what I would do on a table level in a situation where a field is required only if another field is filled in. I guess that's where that MSDE business comes in (if one is to avoid front-end validation rules). "Rick Brandt" wrote in message . .. BruceM wrote: "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. [snip] The basic argument is that if you enforce rules via your front end then the rules are ONLY enforced in your front end. If someone else created another front end linked to your tables then the rules would not be enforced. Or if you yourself end up creating another front end or form to edit the same tables you have to replicate your "code rules" over and over each time. If the rules are enforced by the database engine then they are written once and are automatically enforced in all situations. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#35
|
|||
|
|||
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? |
#36
|
|||
|
|||
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? |
#37
|
|||
|
|||
Roger Carlson wrote: I don't recall talking about clustered indexes at all. That's exactly where you are going wrong. For Access/Jet, you need to start thinking in terms of PK = clustered index. I also DO NOT believe that a clustered index is a requirement for a primary key. In Access/Jet, you get a clustered index with every PK, whether you like it or not. That's why you need to choose your PK carefully. Certainly, EF Codd said nothing about it. This is an implementation issue, not a design issue. Your knowledge of database theory seems to be outdated. Some recycling: Why are we only allowed one PRIMARY KEY per table? Allow me to quote my old pal Joe Celko: "In the first papers that Dr. Codd wrote, he talked about candidate keys -- all the possible keys that exist in a table. Then you were to pick one of them to be called the PRIMARY KEY. "Frankly, this was a hold-over from the days of sequential files -- hey, Dr. Codd did not come up with the whole RDBMS model all at once. When we did EDP (Electronic Data Processing) on magnetic tapes in the 1950's and 1960's, the typical application merged tapes together, so both the master tape and the transaction tapes had to be sorted on the same key (account numbers, or whatever). You do not do random seeks on a magnetic tape. "Very quickly, The Good Doctor realized that a key is a key, and giving a special name to one of them changes nothing about its nature. Primary keys were then dropped from database theory. "However, System R and SQL software had been built on top of old file systems and Dr. Codd's first papers. The PRIMARY KEY was implemented using the existing keys and indexing methods in the old file systems. And it has stayed there since. "Some SQL systems assumed that the PRIMARY KEY would be the preferred access path and optimized for it." This last sentence is significant. The question now is: What special meaning was given to PRIMARY KEY in the Jet implementation of SQL? To cut a long story short, I'll give you the answer: clustered indexes i.e. physical ordering on disk. You can only have one physical order (think paper copy telephone directory: its physical order is fixed) hence only one PRIMARY KEY. In Jet there is no other way of specifying the physical ordering for than to use PRIMARY KEY. If you are using PRIMARY KEY to merely mean NOT NULL UNIQUE then you are under-utilizing it at best. If you designate a sole autonumber as PRIMARY KEY you are certainly using it incorrectly because an incrementing integer (worse, random GUID) makes for a lousy physical order (think paper copy telephone directory ordered on telephone number when your primary usage is to retrieve data by last name then first name). Choosing a bad PRIMARY KEY can result in placing a performance hit on your database. If you decide (and I urge you to resist doing so) to use an autonumber (ID) to force your rows to be unique for uniqueness' sake where you have no natural key, then put it last in your PRIMARY KEY definition and put the columns for your clustered index in appropriate order first e.g. PRIMARY KEY (last_name, first_name, ID) This way, the physical order for the table will be rebuilt in last_name then first_name order with ID merely to satisfy the uniqueness requirement. If you are using autonumber as an artificial/surrogate key on performance grounds e.g. on the basis that a compound natural key is less efficient for table joins etc, then NOT NULL UNIQUE is sufficient. But to use an autonumber for efficientcy then take a performance hit by making it PRIMARY KEY makes no sense! |
#38
|
|||
|
|||
The physical ordering of the records is NOT the most important database
design issue. I've used clustered indexes profitably in SQL Server and in fact did not know you could create a clustered index in Access. (I never claimed to know everything.) But a clustered index is not the be-all-and-end-all of database design. I doubt very much if a custered index on 3 text fields like LastName, FirstName, Address will be more efficient than a non-clustered index on an autonumber field. But even if it is, if the database is that big and performance is such an issue, you should move up to SQL server anyway. I have no more time to waste on arguing with you. If it pleases you to believe you have won, then do so. -- --Roger Carlson Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L wrote in message ups.com... Roger Carlson wrote: I don't recall talking about clustered indexes at all. That's exactly where you are going wrong. For Access/Jet, you need to start thinking in terms of PK = clustered index. I also DO NOT believe that a clustered index is a requirement for a primary key. In Access/Jet, you get a clustered index with every PK, whether you like it or not. That's why you need to choose your PK carefully. Certainly, EF Codd said nothing about it. This is an implementation issue, not a design issue. Your knowledge of database theory seems to be outdated. Some recycling: Why are we only allowed one PRIMARY KEY per table? Allow me to quote my old pal Joe Celko: "In the first papers that Dr. Codd wrote, he talked about candidate keys -- all the possible keys that exist in a table. Then you were to pick one of them to be called the PRIMARY KEY. "Frankly, this was a hold-over from the days of sequential files -- hey, Dr. Codd did not come up with the whole RDBMS model all at once. When we did EDP (Electronic Data Processing) on magnetic tapes in the 1950's and 1960's, the typical application merged tapes together, so both the master tape and the transaction tapes had to be sorted on the same key (account numbers, or whatever). You do not do random seeks on a magnetic tape. "Very quickly, The Good Doctor realized that a key is a key, and giving a special name to one of them changes nothing about its nature. Primary keys were then dropped from database theory. "However, System R and SQL software had been built on top of old file systems and Dr. Codd's first papers. The PRIMARY KEY was implemented using the existing keys and indexing methods in the old file systems. And it has stayed there since. "Some SQL systems assumed that the PRIMARY KEY would be the preferred access path and optimized for it." This last sentence is significant. The question now is: What special meaning was given to PRIMARY KEY in the Jet implementation of SQL? To cut a long story short, I'll give you the answer: clustered indexes i.e. physical ordering on disk. You can only have one physical order (think paper copy telephone directory: its physical order is fixed) hence only one PRIMARY KEY. In Jet there is no other way of specifying the physical ordering for than to use PRIMARY KEY. If you are using PRIMARY KEY to merely mean NOT NULL UNIQUE then you are under-utilizing it at best. If you designate a sole autonumber as PRIMARY KEY you are certainly using it incorrectly because an incrementing integer (worse, random GUID) makes for a lousy physical order (think paper copy telephone directory ordered on telephone number when your primary usage is to retrieve data by last name then first name). Choosing a bad PRIMARY KEY can result in placing a performance hit on your database. If you decide (and I urge you to resist doing so) to use an autonumber (ID) to force your rows to be unique for uniqueness' sake where you have no natural key, then put it last in your PRIMARY KEY definition and put the columns for your clustered index in appropriate order first e.g. PRIMARY KEY (last_name, first_name, ID) This way, the physical order for the table will be rebuilt in last_name then first_name order with ID merely to satisfy the uniqueness requirement. If you are using autonumber as an artificial/surrogate key on performance grounds e.g. on the basis that a compound natural key is less efficient for table joins etc, then NOT NULL UNIQUE is sufficient. But to use an autonumber for efficientcy then take a performance hit by making it PRIMARY KEY makes no sense! |
#39
|
|||
|
|||
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 |
#40
|
|||
|
|||
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? |
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 |