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 |
#51
|
|||
|
|||
Maybe it's cause they don't care?
-Amy wrote in message oups.com... 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.' |
#52
|
|||
|
|||
Amy Blankenship wrote: 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.' Maybe it's cause they don't care? You don't care that there is a difference between NOT NULL UNIQUE and PRIMARY KEY? You don't care to hear something that challenges you conceptions? You don't care that you may give advice that inadvertently falls short of the best advice? I think there is a 'care of duty' to give the best advice one possibly can in these groups. |
#53
|
|||
|
|||
|
#54
|
|||
|
|||
Wow. Take a valium. I think everyone following this thread has absorbed
your point, but you can't make everyone agree with you. That's why we have Democrats and Republicans...because two people can look at the same set of facts and reach different conclusions based on them. That doesn't make either side stupid or ignorant. It just means they see things in different ways. Why do you care if I care? -Amy wrote in message ups.com... Amy Blankenship wrote: 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.' Maybe it's cause they don't care? You don't care that there is a difference between NOT NULL UNIQUE and PRIMARY KEY? You don't care to hear something that challenges you conceptions? You don't care that you may give advice that inadvertently falls short of the best advice? I think there is a 'care of duty' to give the best advice one possibly can in these groups. |
#55
|
|||
|
|||
Amy Blankenship wrote: Wow. Take a valium. I think everyone following this thread has absorbed your point, but you can't make everyone agree with you. That's why we have Democrats and Republicans...because two people can look at the same set of facts and reach different conclusions based on them. That doesn't make either side stupid or ignorant. It just means they see things in different ways. Why do you care if I care? Call me a caring person g. It bothers me that people who are *rightly* considered experts in their field make misstatements which are taken as 'gospel'. This is all a bit 'emperor's new clothes' for me as it is. I don't think prescribing me hypnotics will help, thanks Doctor. "Democrats and Republicans"? This is a global community, so could you provide a global analogy, please? In your country, are political commentators considered sick and/or in need of drugs when they call on politicians to review their policies when new evidence comes to light? I think we should be constantly reviewing our advice to ensure it is the best it can be, especially when we are propagating ideas in public forum. I'm thinking of the common good, the best for everyone. I've nothing to gain personally from this; my MVP status surely having been secured already vbg. What you are currently using PRIMARY KEY to mean you could replace with NOT NULL UNIQUE constraints, then you would be free to use PRIMARY KEY for something more appropriate. PRIMARY KEY may mean other things in other SQL products but in Access/Jet it means 'super unique index'. Is it good advice to recommend using your one 'super unique index' for the table on a single incrementing INTEGER column? I think the professional response would be, "Clustered index? I didn't realize that. EITHER I will now alter the advice I give in future. OR However, after due consideration, I have decided not to alter the advise I give in future because | I think a monotonic INTEGER makes an excellent clustered index. | I would be giving different advice from that of my peers (I've got a reputation to protect). | some other considered reason. I don't think "I don't care" is a professional response. I genuinely respect and thank you for your honesty, though. |
#56
|
|||
|
|||
wrote in message oups.com... Amy Blankenship wrote: Wow. Take a valium. I think everyone following this thread has absorbed your point, but you can't make everyone agree with you. That's why we have Democrats and Republicans...because two people can look at the same set of facts and reach different conclusions based on them. That doesn't make either side stupid or ignorant. It just means they see things in different ways. Why do you care if I care? Call me a caring person g. It bothers me that people who are *rightly* considered experts in their field make misstatements which are taken as 'gospel'. This is all a bit 'emperor's new clothes' for me as it is. I don't think prescribing me hypnotics will help, thanks Doctor. "Democrats and Republicans"? This is a global community, so could you provide a global analogy, please? In your country, are political commentators considered sick and/or in need of drugs when they call on politicians to review their policies when new evidence comes to light? I think we should be constantly reviewing our advice to ensure it is the best it can be, especially when we are propagating ideas in public forum. I'm thinking of the common good, the best for everyone. I've nothing to gain personally from this; my MVP status surely having been secured already vbg. What you are currently using PRIMARY KEY to mean you could replace with NOT NULL UNIQUE constraints, then you would be free to use PRIMARY KEY for something more appropriate. PRIMARY KEY may mean other things in other SQL products but in Access/Jet it means 'super unique index'. Is it good advice to recommend using your one 'super unique index' for the table on a single incrementing INTEGER column? I think the professional response would be, "Clustered index? I didn't realize that. EITHER I will now alter the advice I give in future. OR However, after due consideration, I have decided not to alter the advise I give in future because | I think a monotonic INTEGER makes an excellent clustered index. | I would be giving different advice from that of my peers (I've got a reputation to protect). | some other considered reason. I don't think "I don't care" is a professional response. I genuinely respect and thank you for your honesty, though. How bout "after due consideration, I decided that whether or not it is a clustered index is completely irrelevant to the applications I develop and to most people who post to these groups, who only want to get the job done, so I decided I don't care." The simple fact is that whether or not a primary key creates a clustered index DOES NOT MATTER on a practical level to most developers, as other considerations take priority http://www.dbpd.com/vault/9805xtra.htm. I'd like to point out that while I am a Team Macromedia Member, I am not an MVP at this time. I just happen to be obsessive about answering questions. One thing I have learned after many, many years of giving advice on forums is that it really doesn't matter much how accurate or inaccurate advice is, as users will accept the advice that is most in line with what they believe they can do and also most in line with what they *thought* the solution was going to be. By the same token, the fact that I am not as expert as I am going to be next month or next year (or some other person is now) should not prevent me from offering what help I can, because what help I do offer *is* actually helping people. I doubt *anyone* is currently being helped by your insistence that everyone should care about clustered indexes as passionately as you do. It's a dead horse. Find something more productive to beat. -Amy |
#57
|
|||
|
|||
Amy Blankenship wrote: The simple fact is that whether or not a primary key creates a clustered index DOES NOT MATTER on a practical level to most developers, as other considerations take priority http://www.dbpd.com/vault/9805xtra.htm. Your link suggests you still haven't understood my point (my fault, I fear). I am not saying, don't use an autonumber as a surrogate. I am saying, don't squander you one clustered index on a single INTEGER column that's (a) pretty much guaranteed to be unique and not null by system (autonumber) anyhow and (b) easily constrained using explicit NOT NULL UNIQUE to be doubly sure. Question: do you use indexes specifically for performance purposes? (I mean as distinct from, say, a unique index for data integrity reasons.) If you don't, then I can see why my point keeps flying over your head. Fair enough, you wouldn't be interested and I'll move on another day to someone who is. If you do use indexes specifically for performance purposes then it makes no good sense to disregard clustered indexes. I am not able to reconcile "I choose indexes to enhance performance" with "I choose to ignore clustered indexes". One thing I have learned after many, many years of giving advice on forums is that it really doesn't matter much how accurate or inaccurate advice is, as users will accept the advice that is most in line with what they believe they can do and also most in line with what they *thought* the solution was going to be. I have to say, that one made me cringe. I'm reminded of this old chestnut: quote The analog I used is that if this were a woodcraft group, they post "What is the best kind of rocks to smash screws into fine furniture?" You answer "Your whole approach is wrong. You need to learn about screwdrivers and starting holes. Putting soap on a screw can keep the bosard from splitting. Etc. " They reply: "Screw you! I just want an answer. I want it NOW, NOW, NOW! I don't care that this desk is going to fall apart if I build it this way. And how dare anyone with a few more decades of experience and education in this field try to tell me anything!" Then someone tells them: "Granite. Big huge chunks of granite will smash screws in real good!" And finally the arrogant newbie replies; "See? They helped me!" And they proceed under the idea that anyone can be a master craftsman, as long as they have a pile of five pound granite rocks at hand. /quote |
#58
|
|||
|
|||
The analog I used is that if this were a woodcraft group, they post "What is the best kind of rocks to smash screws into fine furniture?" You answer "Your whole approach is wrong. You need to learn about screwdrivers and starting holes. Putting soap on a screw can keep the bosard from splitting. Etc. " They reply: "Screw you! I just want an answer. I want it NOW, NOW, NOW! I don't care that this desk is going to fall apart if I build it this way. A crude analogy (I assume you meant "analogy"). Databases don't fall apart for want of a "natural" key. Maybe in some cases they don't perform as well. More likely beginner problems are going to be related to things such as storing data redundantly. When you talk about clustered indexes and physical order, and then you include code that includes MAKE TABLE without explaining what do do with the code, the person who is trying to figure out how to manage a few hundred records is left with no clear idea of how to implement your suggestions. When somebody else offers a practical suggestion, which the beginner tries and with which he or she has success, that person is apt to implement the method that works. In another (and I hope more apt) analogy of my own, I think that any serious user of Microsoft Word should learn about styles (as the term is used in Word). But even a person who uses Word extensively will occasionally resort to direct formatting. For the person who uses Word for an occasional letter or something of the sort, I would probably just show that person how to apply direct formatting. I would mention styles, and encourage their use, but not insist on it. Their documents will be just fine. Then someone tells them: "Granite. Big huge chunks of granite will smash screws in real good!" Have you caught on that experienced and thoughtful people disagree with you? You yourself disparage the education and experience of others when it runs contrary to your preferences. And finally the arrogant newbie replies; "See? They helped me!" And they proceed under the idea that anyone can be a master craftsman, as long as they have a pile of five pound granite rocks at hand. "Arrogant newbie", huh? That's pretty contemptuous. I doubt beginners see themselves as database experts. They have a problem to solve, receive suggestions, implement those suggestions, build a functional database, please their bosses, and move on to the many other things they doubtless need to do. Does this make a person arrogant? /quote Consider plain text message format for newsgroup postings. |
#59
|
|||
|
|||
BruceM wrote: A crude analogy (I assume you meant "analogy"). Note 'Analog' (alternatively spelling 'analogue') is the root of 'analogy', however other typos suggest you are correct. "Arrogant newbie", huh? That's pretty contemptuous. Sure is The quote /quote thing was supposed to be plaintext i.e. to tell you I was quoting someone else. Sorry if the point was lost but those words are not my own (excuse me while I adopt a 'How dare you' faux-offended pose g). Have you caught on that experienced and thoughtful people disagree with you? Have you caught on that, because I have a 'broad' understanding of the issue, I can debate the 'autonumber makes a fine PK' from both sides of the fence? This is a good learning technique (devil's advocate) and ensures you don't hold irrational biases. Well informed biases are a different matter and something I have respect for. So allow me to take exception to this of yours: You yourself disparage the education and experience of others when it runs contrary to your preferences. Show me where I have disparaged 'education and experience' for holding an informed opinion contrary to mine and I'll issue an overdue apology. (You know I'm really not Joe Celko, don't you g?) Did you ever try to test a 'point of view' to see if it would stand up? This is the basis of scientific testing (Popper's falsification): you don't test your hypothesis ('All swans are white') directly; rather you test your null hypothesis ('Some swans are not white') and if the null hypothesis can be proven then you must reject your hypothesis. I think that Amy cannot yet put up an effective case for 'This House believes autonumber don't make great PKs', because see can't see it from 'my' side, and therefore is in danger of holding an irrational bias. Reminds me of another star of the philosophy of science, Kuhn's paradigm shifts: (Quote) Revolutions are to be sought on Popper's view also, but not because they add to positive knowledge of the truth of theories but because they add to the negative knowledge that the relevant theories are false. Kuhn rejected both the traditional and Popperian views in this regard. He claims that normal science can succeed in making progress only if there is a strong commitment by the relevant scientific community to their shared theoretical beliefs, values, instruments and techniques, and even metaphysics. (Unquote) [http://plato.stanford.edu/entries/thomas-kuhn/] My take on Kuhn: it would take a revolution to wean exponents from their 'autonumber as PK' habit because people like Amy have their own agendas (e.g. to fit into the MVP crowd) and 'don't care' about advancement for the common good. BTW I don't mean to be harsh on Amy; I admire her for being honest and vocal where many fall silent. And there's nothing wrong with wanting to be an MVP, just don't lose your voice ;-) And BruceM, you are the most fun person round these parts. You listen with an open mind, you respond and you have a voice. Big respect to you, too. |
#60
|
|||
|
|||
You yourself disparage the education and experience of others when it runs contrary to your preferences. Show me where I have disparaged 'education and experience' for holding an informed opinion contrary to mine and I'll issue an overdue apology. (You know I'm really not Joe Celko, don't you g?) Maybe "disparage" was the wrong word, but "You don't understand what PK means" (and words to that effect) is probably not an effective way of trying to win an experienced Access person to your viewpoint. Tends to come across as "You don't know what you're talking about". Did you ever try to test a 'point of view' to see if it would stand up? Yes. This is the basis of scientific testing (Popper's falsification): you don't test your hypothesis ('All swans are white') directly; rather you test your null hypothesis ('Some swans are not white') and if the null hypothesis can be proven then you must reject your hypothesis. I'm not familiar with Popper, but I recognize the logic. Trouble is, it seems to me, there is a subjective element when the discussion turns to which of two systems is better. Faster (and maybe More Efficient) are testable, but may not be significant in the real world. In the absence of detectable (by humans) differences between two systems, "faster" becomes rather abstract. In the computer hardware world, one type of architecture or BIOS configuration or whatever may be faster, but with a performance difference that is undetectable for most purposes. Since I don't do video editing or Auto CAD or processor-intensive things like that, I don't care. I won't bother to upgrade or reconfigure for my purposes. And BruceM, you are the most fun person round these parts. You listen with an open mind, you respond and you have a voice. Big respect to you, too. You are an interesting character, I'll say that for you. Thank you for your kind words. Peace. |
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 08:41 PM |
Sorting a table by concatenating several fields in the same table | salsaguy | Running & Setting Up Queries | 0 | March 6th, 2005 01:33 AM |
Additional fields for form based parameter query/null fields | geeksdoitbetter | Running & Setting Up Queries | 2 | January 7th, 2005 10: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 |