Re: Is "field indexed *with duplicates allowed*" on BOTH tab

Post #107,597 by orion 6/27/03 6:41:35 PM Reply	Re: Is "field indexed with duplicates allowed" on BOTH tab I duplicated his two tables with only serial_number and "serial number" as the only column of the two tables, indexed with duplicates allowed. No primary key was set, Access threw a fit that I didn't set a primay key, but I ignored it. The query works in my version of the database, but then I do not have all the fields that Box's database has, nor do I have any of the relationships set between the two tables. The query works without primary keys set, with just the serial number text fields. I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. Access has a Relationships part of the database that can set Master and Foreign keys. For example he could have a one to many relationship between FooKey on one table, and FooKey on the other, whoch are primary keys, and somehow they are not matching up. The Relationship is not shown in the Access query, but Access is using that Relationship of the FooKey linkage in the query. Of course since I cannot see his Relationships on the database, I cannot tell if there is one that is hosing up the query. It can be accessed via the Tools Menu, and selecting "Relationships" and it will show the tables and have a line between the fieldnames that have a relationship between the tables. If there isn't a relationship set between those two tables, then it could be a damaged database or something else. He trimmed out the extra spaces, and did a search to see that the numbers do indeed exist in both tables. Of course it could also be just as you said, Access is refusing to return any records to avoid a problem like intersecting tables. My database only has a dozen or so items in the tables, his could have thousands and produces a much larger recordset when run. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,607 by boxley 6/27/03 7:20:48 PM Reply	The only primary key is on the master table the primary key is an autonumbered key generated by Access. No other relationships exist except the one serial to serial_number. There is 16k records in the hsinventory and the master table has 4900 approx. The purpose of the exercise is to gather the hostnames from hsinventory to populate the master hostname field so we could gather usage stats from a 3rd table. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,611 by orion 6/27/03 8:22:20 PM Reply	Re: The only primary key is on the master table The primary key in the master table, is it linked to a key in the hsinventory table by the relationships, or are you just saying that you have a primary key on the master table and have no relationship between it and the hsinventory table? "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,627 by boxley 6/27/03 9:35:46 PM Reply	the only link is the serial fields your second sentence is correct. than, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,626 by tseliot 6/27/03 9:35:31 PM Reply	That is a major assumption on your part I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", and second, that's not true for all tables. If the "proper" design were "no duplicates allowed", then why have the option at all? Because some sets are not atomistically unique. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,682 by CRConrad 6/28/03 9:30:20 AM Reply	Actually, for oce Norm is far more right than wrong; you... ...OTOH, are vicey-versy. bob the poet writes: [Quoting Norm:] I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", Well, no -- it is true of ALL databases that ANY two tables in them that you intend to use in a "master-detail" fashion should be linked by a field that is unique in the "master" table. (And since a primary key, by its very nature, won't allow any duplicates, it is the obvious candidate for this.) If, as a not-even-very-charitable reading suggests, Norm is talking about only the case we have here -- how to properly connect two tables _in a "master-detail" relationship_ -- then he is perfectly correct not only in this instance, but for this kind of relationship in all (SQL-based) databases in general. and second, that's not true for all tables. No, but then we weren't talking about "all" tables in general, but more specifically those that are involved _in a "master-detail" relationship_. If the "proper" design were "no duplicates allowed", then why have the option at all? For indexes on fields that are not used to link a "detail" table to a "master" one, of course. And for indexing the link field in the "detail" table, because it is the nature of a "master-detail" relationship that there can be many "detail" records for each "master" record... The uniqueness requirement is only on the "master" table, because there can be only one "master" record for each "detail" record. Because some sets are not atomistically unique. Huh?!? Idunno, suddenly you're using words like "set" (and, Bog help me, "atomistically unique"! WTF???)... Are you talking of "sets" of values of fields other than the one used to link a "detail" to a "master" table? 'Coz if you are, you aren't talking about the same thing as anyone else in this thread. (And besides, if you're talking about "sets" of complete rows from a table, you're factually wrong here too.) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #107,696 by tseliot 6/28/03 1:13:30 PM Reply	We're not disagreeing here. You're just using more words than I did. It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,706 by CRConrad 6/28/03 2:58:36 PM Reply	Ah... that "assumption"! :-) Bobby B writes: It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that. Well, we actually know (now) that they aren't in a "master-detail" relationship, if by that you mean they'd have to have referential integrity constraints defined on them... But on the other hand, they are in a "master-detail" relationship in the sense that that's how they're being used in BOx's query (if I understood it correctly); they are being "related" by the JOIN in the query. At least the latter of my two paragraphs/sentences above is no assumption, on Norm's or anyone else's part. And that the latter should (as in, "ought to") imply the former is also more of a reasoned opinion than an assumption. :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #107,972 by mmoffitt 6/30/03 11:40:08 PM Reply	ALL tables need PK's. Unless you want your sp's to update all "non-atomistically distinct" detail rows in your detail table. bcnu, Mikem The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism. - Mark Twain, "Monarchical and Republican Patriotism"
Post #108,016 by tseliot 7/1/03 10:31:18 AM Reply	Sorry; What's an "sp"? I may try to cram a lot of meaning into a few words, but at least I don't abbreviate where it's just as easy to write it out. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #108,018 by ChrisR 7/1/03 10:36:51 AM Reply	Probably 'Stored Procedures'.... ...but then we were talking about MS Access, so maybe not.
Post #108,047 by orion 7/1/03 12:32:23 PM Reply	In Access they are "Queries" but they work like stored procedures. But I get the idea. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #108,100 by tseliot 7/1/03 4:18:35 PM Reply	Are you disagreeing with me? I can't tell. ALL tables need PK's...Unless you want your sp's to update all "non-atomistically distinct" detail rows in your detail table. Is that not what I said? Or was it the extra step that happened to notice: a sentence written "all...unless" is pretty much the same as saying "not all"? As far as stored procedures go...wait, let me count them in my code...I count nearly... nearly one. Yes, I think that's right--nearly one. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #108,339 by mmoffitt 7/3/03 11:23:33 AM Reply	Dunno if I disagree. You write (in part) wrt Primary Keys: ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", and second, that's not true for all tables. I know the topic is not a real database, but the idea that there exists a database table that does not need a primary key is foreign to me. If that is your view, then I am most definitely in disagreement with you. If you've never worked with a real database, and you don't know that in most secure databases users are prevented from accessing tables directly and can only access the data in the tables through stored procedures and views (and you definitely want insert/update/delete sp's - and yes that's stored procedure), then it's not surprising that you hold that primary keys are unnecessary on some tables. But, you're absolutely wrong if that is your view. Even with toy databases you should put a PK on each table. Doing so can save yourself grief should your little app ever need to scale up. Not doing so is a violation of "good design". bcnu, Mikem "Son, if you haven't got time to do it right the first time, where are you going to find the time to do it a second time?" - Dewey Keller, my grandfather
Post #108,362 by tseliot 7/3/03 2:10:46 PM Reply	Toy and scale are not the issues. Design is the only issue I'm talking about. Not every table has unique elements, because not every Set of objects in the real world has unique elements. Example: you have 30 tool packages you can rent out to customers, all the same. There is no distinction between them. There never will be a distinction between them. That is the whole point: they are the same in every way. A table which models these 30 tool packages does not need a primary key. You might feel safer with one, but that does not erase the fact that there is no need for one. All that matters is that we do not oversell them. A primary key does not assist in this process at all. Would it hurt to have one? Probably not. If you want the overhead, be my guest. I can just see kindergarten class: "Now if Jimmy has five apples, and we take away 3 apples.." "Ooh, ooh, wait! Which three are we taking away?" "It doesn't matter, just subtract three." "But which three?" "It doesn't matter!" "Oh, it most certainly does. Why don't these apples have barcodes so we can tell them apart? Billy, go get the label maker! Susie, find us a workbench and clamp! Margaret, you start cutting the crystal for the laser...we need a reader..." Overkill. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #108,369 by CRConrad 7/3/03 3:51:15 PM Reply	Then you don't need a thirty-line table at all... ...but a numeric variable `NumberOfToolSetsRented` somewhere, that fluctuates between zero and thirty. Worst case, you have to have a (one-line) table to keep it in. Then you can just update that table (increase or decrease the value by one) every time a toolset gets rented out or returned... And the way to do that is, of course, by using a stored procedure. Oh, and if you ever find you only have twentynine toolsets on hand, and want to know which customer didn't actually return his as he was supposed to, then what do you do? Then you wish you'd bar-coded your toolchests and used the `ToolChestID` value of the barcode as a primary key, that's what do you do. [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #108,404 by mmoffitt 7/3/03 10:56:12 PM Reply	Thank you. bcnu, Mikem The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism. - Mark Twain, "Monarchical and Republican Patriotism"
Post #108,435 by FuManChu 7/4/03 1:18:46 PM Reply	Should I answer? This is becoming religious and I don't see anyone convincing anyone else. You've all got your methods; use them. I have an extra tool that I can use with or without your blessing. So I'm not going to beat this example to death; it was a bad example from the beginning because it only gains significance if scaled up, which was part of the issue I was trying to avoid. If you can't generalize the concept from the example, then again we go nowhere, and the gain frankly isn't worth it even if one of us happened to convince another. The only point I WILL make at this juncture, is this: Then you don't need a thirty-line table at all...but a numeric variable `NumberOfToolSetsRented` somewhere, that fluctuates between zero and thirty. Um... get thee behind me? This is, IMO, a far WORSE solution, since you are now hard-coding minute details of the transaction INTO A FREAKING VARIABLE NAME. What was that about scaling up? I give it...six months before such a horrible hack would get taken back out of the code. Make an invoice object with a `Qty` property, maybe, but `NumberOfToolSetsRented` is going too far. I'm gonna go build my own theme park! With Blackjack! And hookers! In fact, forget the park!
Post #108,395 by a6l6e6x 7/3/03 9:56:56 PM Reply	But, but, a tool of any value has a serial number and they.. are different. Warrantees or support, if any, are tracked by serial number by the manufacturer. Suppose, on return, your clients substituted a barely functioning tool of the same model for one they rented. How would you know? Are we talking hammers here? :) Alex The tendency to turn human judgements into divine commands makes religion one of the most dangerous forces in the world. -- Georgia Harkness in "Conflicts in Religious Thought" (1929)
Post #108,436 by FuManChu 7/4/03 1:24:29 PM Reply	In this particular case Suppose, on return, your clients substituted a barely functioning tool of the same model for one they rented. How would you know? Are we talking hammers here? :) We are talking hammers, at my company. So "barely functioning" is no different than "functioning". Components of the toolkits (like hammers, shovels, etc.) get damaged and lost all the time, and that gets noticed and dealt with by humans at the time of restock. The cost of numbering would far exceed any benefit, since the toolkits often get moved from site to site "on the fly", without time to come back to base camp. I'm gonna go build my own theme park! With Blackjack! And hookers! In fact, forget the park!

Welcome to IWETHEY!