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 |
#61
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Jamie Collins wrote:
Consider if you have a parent and subform based a composite PK? That's a lot of extra work there figuring out what fields go where. This implies you design your database schema (tables etc) for the convenience of your front end (forms and subforms) and/or for your own convenience?! That would be "and" not "and/or". And yes that's a small part of the reason yes. My approach is to design the database schema entirely independent of the front ends (which is done by another person anyhow). I do everything. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#62
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Jamie Collins wrote:
In an ideal world, surrogates would be implemented under the hood and not even be exposed to DBA, developers, etc. I encourage people to use the random flavour of Autonumber to discourage this practice I disagree. Sometimes when trouble shooting problems it's a lot easier to remember two or three digit numbers when viewing data from different tables. I'm using my own test databases with a few or a few dozen records. Later I'll test against client databases. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#63
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
M. wrote:
What still interests me, however, is how pepole prevent entry of duplicate records when they use a autonumber field as primary index. This choice doesn't offer you any guarantee that the real data in your record is accidentally entered a second time in a later record. In my opinion you would still need a unique single (e.g. ISBN number) or multifield index (e.g. book title + author name) to prevent duplicate entry. In the end, it's then a matter of taste whether you name the autonumber field index primary or the composite field(s) index primary. Like Dale said I use either unique indexes on other fields if applicable and which surprisingly often aren't possible. So now you have to use some business logic and depend on humans. Book titles can be duplicate (Golden is one example with two different authors in 2006) as well as having multiple authors, translators, illustrator, etc. Humans can have duplicate names and birth dates. Or Fred Jones and his son Fred Jones live at the same house. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#64
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 7, 5:37 pm, Dale Fye wrote:
I design my schemas as close to 3rd or 4th normal form as feasible. Doubtful g. 3NF is a historical anomaly and 4NF is rarely desirable. I'd wager you actually achieve BCNF and 5NF more often than you think But, rather than carry around the excess baggage of a 3, 4, or 5 field PK, I have elected to take the path that is most efficient given my chosen front/back tools (Access). However, even when I get the opportunity to take advantage of the server side processing that SQL Server provides, I still use a 'meaningless' PKs to speed development (makes my job easier) and minimize the risk of mistakes (as one of the other posts mentioned, an update query where one of the joins is missing can be disasterous). It seems you use a so-called surrogate (what Codd meant by 'surrogate' is open to debate e.g. when he said users "have no control over its value, nor is its value ever displayed to them" did he mean DBAs and Access Developers?) for FK references. Well, that's your design choice and, assuming you have table constraints (rather than front end code) to cover your candidate keys, then fair enough; I'd hope you wouldn't use another's threads to evangelize g. However, note the essence of the OP's question seems to me to be: given that someone has used Autonumber as a so-called surrogate, what reasons would they have for not making the Autonumber the PK? I cordially invite you to attempt to answer that question. I ... take great exception with programmers who kludge their database together based on their desired form layout. Then why did you reply to me rather than take great exception with Tony Toews [MVP] who made the point...? Don''t forget to rate the post if it was helpful! ....Ah, perhaps I see why ;-) Jamie. -- |
#65
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 8, 7:46 pm, "David W. Fenton"
wrote: My approach is to design the database schema entirely independent of the front ends (which is done by another person anyhow). I'll bet you have developers cursing you all the time. We have design reviews where discussion is encouraged. Cursing is not a great way of raising an objection, IMO. I learn a lot about how the properties and structure of the entities represented in database schema in the process of designing the UI. It's a two-way process, with feedback flowing from the schema design to the UI and back again. This is not to say that the schema design is driven by the UI, only that in designing a UI *for people* one can learn things about the schema that were not in the spec, or not obvious from it. If you are saying that your logical model is only as good as the domain expert's spec then I am in full agreement. Jamie. -- |
#66
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 8, 7:51 pm, "Tony Toews [MVP]" wrote:
In an ideal world, surrogates would be implemented under the hood and not even be exposed to DBA, developers, etc. I encourage people to use the random flavour of Autonumber to discourage this practice I disagree. Sometimes when trouble shooting problems it's a lot easier to remember two or three digit numbers when viewing data from different tables. I'm using my own test databases with a few or a few dozen records. Later I'll test against client databases. And if you make it the PK, your client and their users will end up with the double whammy of poor concurrency and poor clustering. But, hey, the convenience of the developer is what counts for you, eh g? Jamie. -- |
#67
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 9, 5:04 am, "Tony Toews [MVP]" wrote:
I use either unique indexes on other fields if applicable and which surprisingly often aren't possible. That's why we were given table-level CHECK constraints. Book titles can be duplicate (Golden is one example with two different authors in 2006) as well as having multiple authors, translators, illustrator, etc. I sure wish someone would hurry up and invent an industry standard identifier with a trusted source which will be familiar to users. Oh, hang on: they already did (ISBN). Humans can have duplicate names and birth dates. Or Fred Jones and his son Fred Jones live at the same house. Fred Jones's credit card number is unique and he is not supposed to disclose his PIN number but how do you know it is not his son Fred Jones using it? Fred Jones has different fingerprints to Fred Jones but are you authorized to retain that data? It's all about trust i.e. you need a trusted source for identifiers. An Autonumber in an Access database cannot verify people in reality therefore cannot be used as a trusted source. Yes that's a straw man argument, as are your faulty examples of natural keys. Jamie. -- |
#68
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
If you want to conduct an intellectual discussion, you really need to be
able to handle it when people disagree with you and stop taking cheap shots. "Jamie Collins" wrote in message ... On Dec 8, 7:51 pm, "Tony Toews [MVP]" wrote: In an ideal world, surrogates would be implemented under the hood and not even be exposed to DBA, developers, etc. I encourage people to use the random flavour of Autonumber to discourage this practice I disagree. Sometimes when trouble shooting problems it's a lot easier to remember two or three digit numbers when viewing data from different tables. I'm using my own test databases with a few or a few dozen records. Later I'll test against client databases. And if you make it the PK, your client and their users will end up with the double whammy of poor concurrency and poor clustering. But, hey, the convenience of the developer is what counts for you, eh g? Jamie. -- |
#69
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Jamie Collins wrote:
I disagree. Sometimes when trouble shooting problems it's a lot easier to remember two or three digit numbers when viewing data from different tables. I'm using my own test databases with a few or a few dozen records. Later I'll test against client databases. And if you make it the PK, your client and their users will end up with the double whammy of poor concurrency and poor clustering. But, hey, the convenience of the developer is what counts for you, eh g? How does clustering apply to Access databases? And I've only ever had one problem with concurrency in all these years. So not a problem for me. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
#70
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
On Dec 10, 3:32 pm, "Pat Hartman" please no wrote:
If you want to conduct an intellectual discussion you really need to be able to handle it when people disagree with you and stop taking cheap shots. If I really was conducting this discussion I would have answers to my direct questions (ping Dale Fye: "given that someone has used Autonumber as a so-called surrogate, what reasons would they have for not making the Autonumber the PK?") And if I couldn't handle people around here disagreeing with me I'd have left years ago! FYI: The Access Web: Netiquette http://www.mvps.org/access/netiquette.htm Be thoughtful of bandwidth and other folks' thought processes: [quote] Look for Smileys :-), grins g, and other "Emoticons". When you see one, the preceding statement was not meant to be really serious, don't take it as such. [Unquote] I don't think the poster was being serious either (hence my short dismissal with a grin). Designing database for one's own convenience rather than your client's doesn't sound like a credible proposition to me. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|