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 |
#191
|
|||
|
|||
Separate PK in Jxn Tbl?
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... Sorry if I didn't responded before but I wasn't sure to understand what you have wrote. I partage your opinion that a composite key can not only unequivocally identifying any item in a particular database but also remain constant (ie. never change from database state to database state) in many systems. But as I said, this is not true for all systems and there are occasions where the value will change from state to state. For instance, is instead of an inventory system you have a sport ligue system, with a table making a jonction between a list of players and a list of team; it's easy to see that in such a table, the composite key may change its value from state to state; for example when a player is exchanged between two teams. I was just pointing out that there are times when composite key values and natural key values permanently identify individuals, and in those instances, the autonumber primary key is just an added complication that serves no material purpose. One can claim that they speed up queries, when in fact they they slow things down due to the additional indexes that need to be maintained. And as has been shown in other posts, the number of joins necessary to answer even simple queries increases with the use of autonumber primary keys, so in fact they may degrade query performance. I don't want to enter into a discussion of the full range of possibilities (for example, do you want the database to remember the previous relationship, what about the player number (if a player change his/her number, do you want to keep the older information?), the individual statistics, etc., etc.) but it doesn't take too long to see that in such a situation, the use of a composite key to express the relationships between tables will rapidly become like hell. Like someone else has said: « been there, done that » and personally, it's not my intention to go back there. Everyone know that when it's time to make a decision, one personal experience has more weight than a thousand opinions so for me, my first reaction about using a composite primay key will be a no go. I think that if you distill all of the reasons you're citing, they all boil down to whether or not a key's values are permanent identifiers or not. If they're not, and there is a need that they be, then the addition of a permanent identifier may be necessary. Now whether that's an autonumber or some other kind of surrogate isn't really that important. But there are other occasions, like your inventory system, where the possibility that a composite primary key can change its value don't exist. In these occasions, would it be overkill to use a separate primary key? Personally, I don't mind using a separate primary key even on these occasions but I fully understand that other people might feel unconfortable to do the same. Everyone know that the devil like to hide in the details; probably that besides the details, the composite keys are also one of his favorite places to hide. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Brian Selzer" wrote in message ... "Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... To that, I would add that the increased simplicity of using a surrogate (or artificial or autonumber) key as the primary key in place of a composite key is only half their advantage. The biggest problem that I have with composite keys is that they share the same fundamental problem as natural keys: using them as the primary key is allowing the fact that a primary key can change its value over time. IMHO, a primary key should never be allowed to change its value once it has been created; a assumption which will forbid the use of a composite key in many cases. (Of course, if you don't mind to see a primary key changing its value after its creation then you are not concerned by this argument.). This argument has an inherent fallacy in it. Just because a key is composed from multiple columns doesn't necessarily mean that its values can be different in different database states. For example, in an Inventory table that has the key, {ItemKey, WarehouseKey}, with references to an Item table and a Warehouse table respectively, the combination values that comprise each key value can never change from database state to database state. A particular combination of values identifies a particular individual in the Universe of Discourse in /every/ database state in which it appears. It can /never/ identify any other individual. Therefore, it should be obvious that adding an additional autonumber primary key in this instance would be superfluous, since each {ItemKey, WarehouseKey} combination already rigidly designates a distinct individual in the Universe of Discourse. The same can be said for many natural keys. For example, suppose you have a table, Queue, that has an integer key, {Position}. Each value for Position rigidly designates a distinct individual in the Universe of Discourse (3 always means "third in line" in any database state in which there are 3 or more elements), so therefore there is no need for an additional autonumber primary key. This is not only a theoritical argument as many interfaces - like Access - won't like to see a primary key that could change it value. But even if you take out such interfaces out of the equation, the use of a surrogate key for all tables reveals itself to be advantageous in many database problems. For example, if you want to add a log of all changes to a table, it's much more easier to design it if the table use a surrogate key for its primary key than a natural key or a composite key. Personally, I stopped using natural keys and composite keys many years ago and probably that something like half of my problems with the design of databases have vanished with them. On these occasions when I was called to work on a problematic database, chances was much higher to see that the problems were associated with the use of natural keys and/or composite keys than with the use of a surrogate keys and the solutions were usually much more complicated to solve in the first case than in the second case. Also, I've remember some peoples who have done like me and have stopped using natural and composite keys in favor of the exclusive use of surrogate keys but I don't remember anyone doing the opposite; ie. going from the use of surrogate keys to the use of natural and composite keys. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) wrote in message ... On Jan 25, 9:12 am, Jamie Collins wrote: On Jan 24, 11:00 pm, "James A. Fortune" wrote: Whenever I have multiple key fields, natural or not, I create an AutoNumber PK for pragmatic reasons. The main reason is that it makes it easier to create the joins. The theorists are champions at joining tables and don't have to be concerned with the complexity of the SQL they write. Word to the wise: 'theorists' hate SQL. Queries involving many to many relationships often add additional tables later and highlight the need to keep joins as simple as possible. I think I'm with Bob Badour (gulp!) on the issue of complexity, though: if you think more columns in the ON clause makes a SQL join more 'complex' then I think you could be looking at things wrong. Having more characters to type increases the risk of typos? More columns mean you may omit one in error? The SQL engine may be twice as slow in handling two columns rather than one? Is it more 'complex' to split a post address into 'subatomic' columns (address lines from postal code/zip etc)? Surely the issue you allude to (I think) is the one that Access Relationships (as distinct from Jet foreign keys) were invented to solve? i.e. you pre-define the join columns and 'join type' (inner join, left outer join or right outer join) and the join clause gets written as SQL for you when you drop the tables into the Query Builder thing. I would have thought the 'theorists' would love the fact that you also create foreign keys in the same Relationships dialog i.e. you end up with a natural join (not having to explicitly specify the columns yourself) because one table references the other. [I tend to be dismissive of tools that write SQL code for me but I think I should perhaps review my stance e.g. I still write all my SQL Server procs by hand whereas I have tasked myself to investigate CRUD generators. But, for the time being, ...] As a SQL coder myself, I find it more annoying that I have to create multiple joins to get the 'natural key' values, having to discover what the 'artificial key' columns are in the first place. Lately, I've increased the amount of normalization in one of my databases and the joins got even more complicated, adding about a line or so in the SQL view in Access for every new query using those tables. Bad luck: I think you might have got way with "reduced the amount of denormalization" ;-) In this thread I've already broken my personal rule (!!) about not mentioning normalization [formulated because the average 'replier' around here thinks "fully normalized" is BCNF, which they think is 3NF anyhow, and doesn't pay much attention to anomalies that normalization doesn't address, unless the 'asker' mentions storing calculations...] I keep Jamie's advice in the back of my mind, about how enforcing constraints at the table level is better than enforcing them through code ..and best to do it in both places! Bear in mind that it's a rule of thumb i.e. "strict rules modified in practise." Checking something in the in front end allows you to give timely user feedback and could save them some keying, not to mention a database roundtrip. Checking in the database catches anything neglected in the front end by omission of validation or introduction of bugs. In practice, some things are better done in one place but not the other: contrast the validation of the basic pattern of an email address with the verification that an addressable entity can be contacted at that email address; I don't think it would be sensible to put the latter test into a table constraint, even if it were possible. Jamie. -- What part of simpler don't you understand :-). Only one expression in the ON is simpler. Needing less indexes is simpler. Not having to look for your multi-key fields is easier, although your point that Relationships can handle that is valid. If the AutoNumber key has a one-to-one relationship with the multi-key fields then it's fine to use it. There's no down side that I can see. I also like to rely on coding to detect inconsistent data rather than on error trapping, so I have to check the multi-key values anyway before adding a new record. I think that your idea about enforcing constraints at both the table level and in code is an excellent idea. The OP wanted to know what people did and why. I still don't see any reason put forward for me to change to a multi-field key. Are totals queries easier when multi- field keys are used? BTW, "reduced the amount of denormalization" works just as well. Real databases experience denormalizing influences. James A. Fortune |
#192
|
|||
|
|||
Separate PK in Jxn Tbl?
"Marshall" wrote in message ... On Jan 29, 7:56 am, "Neil" wrote: "Jamie Collins" wrote in message Here in the UK I avoid using the word 'moot' when trying to write 'plain English' simply because the US usage has obscured the UK usage i.e. it can cause confusion. You could use the alternate "moo" point, meaning that the point is full of methane gas. At least it would be clear what you mean.... ;-) Amusingly, a lot of people misperceive the word as "mute." It's a mute point, meaning it can't say anything any longer. It sorta vaguely works in a metaphoric way. Of course, on the internet, you can find many examples of fractured usage. Ultimately it's just a waist of time.snicker Reminds me of a time that I was laughed at by a group of coworkers for insisting that the phrase "that doesn't jibe" is correct, and "that doesn't jive" (as they insisted) was incorrect. J-j-j-jive talking.... I also love the usage of "nip it in the butt" instead of "nip it in the bud." One time I corrected someone for using "butt," and she apologized, thinking I was offended by her use of the word "butt," and was creating my own "softer" version with "bud" instead. Hahahaha! |
#193
|
|||
|
|||
Separate PK in Jxn Tbl?
David Cressey wrote:
"JOG" wrote in message ... On Jan 27, 2:18 pm, "Rick Brandt" wrote: David Cressey wrote: The idea of keeping garbage out of the database takes on an entirely different meaning if you are dealing with hundreds of programs written in COBOL, Java, or anything in between accessing a single Oracle database on the one hand. On the other hand, if you are a developer creating a self contained MS Access database cum application (tables, queries, forms, reports, modules, etc.) all in one file, the same issues arise, but they are resolved quite differently. I'm not saying either one is "right" or "wrong". I'm just suggesting why an objection that makes perfect sense to you and me might be lost on the MS Access community. ^ some of I think if a thorough poll was done it would show that the majority of professional Access developers (those that make their living at it) would agree that data integrity rules should be enforced by the database engine wherever that is possible. Well thank goodness for that! For a scary moment I though Sylvian's views were representative of the access community as a whole, and that you guys didn't think that data integrity should be enforced primarily by the db engine. The fact is that Access is a tool predominantly for *users*, not developers, and Microsoft appears determined with each subsequent version to make that more the case. The majority of changes make it easier to do things incorrectly because that makes the program easier to use for people who have no idea what they are doing. Since that group vastly outnumbers the other one can hardly argue with their logic from a business standpoint. My fear though is that many db developers cut their teeth using Access. If bad practices are encouraged just because access doesn't handle many concurent users, and tends to manage data where it's unlikely one will hit the pitfalls that artificial keys can lay, when developers graduate up to larger server systems they may well carry those mistakes on with them. I agree with you. However, we should keep in mind that the same arguments could be made about people learning bad programming habits by building amateur programs in BASIC, or bad website design habits by using a tool like Front Page. Hmmmm... MS Access, MS Basic, MS Front Page... anyone notice a trend? In general, the tools that require a very short learning curve encourage the belief that the longer learning curve is of no practical value. It doesn't help when vendors, whose own employees know better, encourage life-long ignorance among their customers. We've seen that view voiced here (perhaps facetiously) by one of the Access MVPs. To the extent that he has acquired a lot of credibility with Access newbies, however acquired, if he gives advice that will become bad advice when scaled upward, he aggravates the pitfall you warn against. Elsewhere in the discussion, I opined that Access applications were generally stored in the same file as the database. I've heard enough contrary opinions to stand corrected on that score. (I can't find that part of the discussion anymore.) However, I still think that hundreds of DIFFERENT application programs accessing a single database and written by programmers who did not build the database, is qualitatively different from the design target of the people who write Access databases and applications. Years ago, I heard that the median Access application used a single table with 500 rows. I wonder whether that has changed any. If they ever get to the point where the complexity of what they are doing matches the complexity of what practitioners using SQL Server, Oracle, or DB2 are doing, or the complexity that database theorists are addressing, they will be forced to either learn or disprove what some of us know, or think we know. Access is a good end-user query tool. The problem is some are deluded into believing it should instead be a crappy application development tool or a ****-poor data management tool. I certainly don't think developers should excuse sloppy RDBMS design just because they are using access (and of course I'm sure many of the professionals here wouldn't dream of doing so, despite others laxness). I have to admit that, when I'm just playing around, I engage in sloppy work. I would not go so far as to recommend sloppy habits as good ones in a newsgroup, however. This is why, in spite of our many differences of opinion, you never seem to make my kill file. Your opinions and anecdotes are clearly designated as such and never passed off as the state of the art. |
#194
|
|||
|
|||
Separate PK in Jxn Tbl?
Salad wrote:
Sylvain Lafontaine wrote: I concede the point that for the two keys of the junction table, using an autonumber primary key is overkill except for special situations. Shouldn't a database be designed right from the beginning? I prefer KISS. Well, the second S is right. Thus I prefer an autonumber. So, adding a useless third column makes it simpler?!? Then again, junction tables are rarely needed. That's a remarkable statement requiring extraordinary evidence. What evidence do you have to support such an assertion? |
#195
|
|||
|
|||
Separate PK in Jxn Tbl?
Sylvain Lafontaine wrote:
I concede the point that for the two keys of the junction table, using an autonumber primary key is overkill except for special situations. Shouldn't a database be designed right from the beginning? I didn't say overkill doesn't work, did I :-)? I think that until we delineate the true trade-offs between natural keys and artificial keys, if any, you should design your schemas/schemata as you deem best. If it turns out that there are situations where each has advantages then those situations should determine the correctness of the schema. James A. Fortune |
#196
|
|||
|
|||
Separate PK in Jxn Tbl?
Tony Toews [MVP] wrote:
Frank Hamersley wrote: Therein lies its criminality g - it screams encouragement for dabblers and barely offers anything for artisans except stupendous numbers of mouse clicks! OTOH do you want them using Excel for data management? Single user? Urrk - where is my garlic smeared wooden stake? [..] And, of course, Excel is single user as far as I know. But for how much longer? Don't get me wrong as I spend nigh on 80% of my day "in" Excel - but only as a tool in financial markets - not as a repository! Take for instance the number of versions it took before separating the data from the "code" was a core feature by way of the provision of a menu option to reattach a data .mdb! A97 has the wizard. I don't recall about previous versions. FWICR 1.0 had nothing, 1.1 you could get at the "object" using AB on a sleight of hand styled bending of a path+file attribute as I recall. Then came the wizard but if you were distributing code .mdb's out to remote sites you still need to AB it so you could make it part of your own app. Of course the dabbler doesn't contemplate such when making promises about future glory for all. Cheers, Frank. |
#197
|
|||
|
|||
Separate PK in Jxn Tbl?
Do you mean a bit like there are ISBN-10 and ISBN-13. Should I avoid using them and roll my own 'BookID'?
LOL! The US recently converted from the old UPC codes which were 10 digits to the 13 digit EAN system (ISBN13 was only a small part of this) and will convert to the 15 digit GTIN in the near future. Yet somehow we survived and nobody started printing their own bar code labels again (yes, I am old enough to remember retail without UPC on the packages). The "immutable data" fallacy is that the identifier must get me to the actual entity when I use it; it is free to mutate as it wishes as long as it does its job. I have seen a security system in which the user id is changing every five minutes or faster to prevent hacking. It works great, but requires a memory stick in your PC to keep up with things. The advantage of standardized encodings is that I have a migration path that is sponsored by an entire industry instead of having to do everything myself. |
#198
|
|||
|
|||
Separate PK in Jxn Tbl?
I have yet to have any of them rise to the challenge of siting a single example of a correctly set up, split application in Access that isn't stable under load.
Home Depot's contractor tracking system on ACCESS? It's failures are famous in Atlanta. |
#199
|
|||
|
|||
Separate PK in Jxn Tbl?
David Cressey wrote:
database, is qualitatively different from the design target of the people who write Access databases and applications. If they ever get to the point where the complexity of what they are doing matches the complexity of what practitioners using SQL Server, Oracle, or DB2 are doing, or the complexity that database theorists are addressing, they will be forced to either learn or disprove what some of us know, or think we know. I don't have broad enough experience to dispute your argument. I understand that people who specialize in SQL and deal with more complex situations than most develop practices that make use of their more intimate knowledge of SQL. However, I can't just take their word about their decisions. I have to understand how those choices apply to what I'm doing. Without making light of their potential contribution, I avoid the specious argument that because a large company or IT department does things a certain way or spends more money on the problem makes their solution inherently correct. Plus, the complexity of the problems they face often argue against their use in Access. Few Access developers have the luxury to hire or supervise a full-time SQL developer. If using multiple field natural keys causes a problem(s), a full-time SQL developer has time to work with the SQL until the problem is solved. SQL is only part of our job. It has been nice to see posters in microsoft.public.access such as Jamie Collins and Ken Sheridan, who seem to have a lot of standard SQL experience, branch off into other issues that Access programmers face. I think their understanding of those issues can help us differentiate between purely SQL issues and Microsoft implementation issues. We all agree that Microsoft has made questionable design decisions in Access, but the possibility of inclusion of unbound forms or of AutoNumber primary keys might not be part of that list, as some have suggested. I have enough experience to say that using unbound forms in Access and using artificial keys did not cause any problems when scaling an Access application up to an ASP solution using SQL Server. For something more complicated perhaps there are subtle issues that arise that merit our attention. Table level constraints also raise an issue. At the table level, there is no VBA code run to ensure that any constraints (e.g., on the natural keys themselves) are enforced. Thus, a set of natural keys is sufficient to specify the key constraint. Any artificial key becomes superfluous. The possibility of separate applications using the same table, brought up by David Fenton, brings up the interesting possibility that the applications have separate, possibly disparate constraints. In disparate constraint situations not all of the constraints can be at the table level, although they can be specified at the query level in each application rather than via code. Maybe many Access programmers prefer a single key to limit the number of fields that get corrupted :-). Perhaps an enumeration of sticky situations encountered by those using multiple field natural keys would help us understand why some avoid them. Maybe better solutions to those sticky problems would encourage developers to stay with natural keys more often. James A. Fortune |
#200
|
|||
|
|||
Separate PK in Jxn Tbl?
-CELKO- wrote:
I have yet to have any of them rise to the challenge of siting a single example of a correctly set up, split application in Access that isn't stable under load. Home Depot's contractor tracking system on ACCESS? It's failures are famous in Atlanta. You object of derision is the application whereas an outsider like me would assume management, specs, and skill level created the failure. AFAIC, the programmers in CDMA are world class folks and obviously were not part of the project you brought up. Dilbert management providing ill thought out/band-aid specs while employing low-level programmers that don't speak English can create a famous failure. By chance are you on the failure's committee? Do you have any links to the famous failure you mentioned? It might make for a fun read of management incompetency. If you do respond, be sure to cut out all my post so nobody knows who you are responding to and make it appear you are talking to yourself for no particular reason. |
Thread Tools | |
Display Modes | |
|
|