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 |
#111
|
|||
|
|||
Separate PK in Jxn Tbl?
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... This remind me of the discussion twenty five years ago between relational databases (like SQL-Server) and non-relational databases (like ISAM databases, dBase, etc.). It has always been right to say that for simple queries, non-relational databases are faster than relational databases. However, nowadays, non-relational databases have (practically) vanished because of their inherent slowness when the queries become more and more complicated. This is exactly the same situation with the possibility of accelerating a query by using a natural key: you are accelerating simple queries that are already light and fast but on the opposite side, you are slowing down complexe queries that are already big and slow. Not sure if going this way is really advantageous. I don't think it's exactly the same situation. As long as the data resides in the same table, it is possible to create several indexes over several columns in order to improve query performance--including join performance. It's a lot harder to do that when the data is spread out over several tables, as can be seen from the artificial key example. (You could use indexed views, perhaps, if you know ahead of time which queries will be run.) -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Brian Selzer" wrote in message ... wrote in message ... On Jan 27, 12:39 am, "Brian Selzer" wrote: "James A. Fortune" wrote in messagenews:% Access programmers use forms to interact with the data. If I follow Jamie's advice and constrain the data at both the table level and in code, then your points make more sense. Right now, they're just arguments for me not to constrain the data at the table level because the reasons you gave might make natural keys preferable in that situation :-). Well, that's just dumb. Checks in code can reduce database round-trips, and therefore can improve performance, but are not and cannot be a substitute for constraints on the tables. It is the constraints on the tables that keeps garbage out of the database. If the users only access the tables through forms, conforming to best practices in Access, how are they going to get garbage into the tables? Now if you're trying to keep Jamie and his Excel SQL out of your database, that's another story :-). There can be several forms that access the same table, so you would have to duplicate the code behind each form that accesses a table, or you can get garbage into the database. * Referencing an artificial key in a child table can complicates queries - and not just with a longer restrict clause, but with a whole extra join that may well have been unrequired if a natural key had been used. I don't agree with that point. The child table can contain the AutoNumber primary key from the main table as a foreign key if desired. I don't see how using the natural key fields requires less joins than that. Maybe an example would help me understand what you mean. An extra join may be needed if the natural key from the parent table is used in a restrict clause. If all you have is the artificial key from the parent table, then you have to join in order to access the natural key columns. With natural keys, the natural key values from the parent table also appear in the child table, so there isn't any need to join. Bottom line: joins of artificial keys are typically faster than joins of natural keys due to the size of the comparands, but with natural keys, fewer joins may be needed.. If you're planning on using a natural key column in the child table as part of a join then doesn't it make sense to include that field in the child table? Still waiting... A typical schema with artificial keys: Customer {CustomerKey, CustomerNo, ...} Key {CustomerKey}, Key {CustomerNo} Item {ItemKey, ItemNo, ...} Key {ItemKey}, Key {ItemNo} CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo} Key {CustomerItemKey}, Key {CustomerKey, ItemKey} CI[ItemKey] IN Item[ItemKey] CI[CustomerKey] IN Customer[CustomerKey] SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price} Key {SOLineKey}, Key {SOKey, SOLineNo} SOLine[CustomerItemKey] IN CI[CustomerItemKey] A typical schema with natural keys Customer {CustomerNo, ...} Key {CustomerNo} Item {ItemNo, ...} Key {ItemNo} CI {CustomerNo, ItemNo, CustomerItemNo} KEY {CustomerNo, ItemNo} CI[CustomerNo] IN Customer[CustomerNo] CI[ItemNo] IN Item[ItemNo] SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price} SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo] Now write a query that returns how many of item '12345' were sold to customer '4321' It should be obvious that with the natural keys, no joins are necessary--it's just a simple select from SOLine since all of the information is actually /in/ SOLine; whereas, with the artifical keys, several joins are required because in order to query by item number and customer number, SOLine must be joined to CI which must then be joined to Customer and Item. |
#112
|
|||
|
|||
Separate PK in Jxn Tbl?
Much of the early (Bowdlerized) description of relational databases was written with reference back to the (at the time) more familiar CODASYL databases.
I was on X3H2 when we were still working on NDL and I have a copy somewhere next to my COBOL manual autographed by Grace Hopper. |
#113
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 27, 8:33 pm, "David W. Fenton"
wrote: JOG wrote m: 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). What *are* you talking about? Any mistakes in schema design that you can make in Access, you can make in any other RDBMS. *Sigh*. Yes, but as bob has pointed out, you've misconstrued my point. Because it is marketed at different business problems (ones with few concurrent users, simple domains, comparatively smaller schema), a lot of Access users can get away with mistakes that someone using, say, Oracle 11g to keep track of millions of facts would in the end get called up on. So that's nothing to do with the technology, just the market, which makes your empassioned defence of the super-duper jetomatic engine a bit misplaced. I'll also ignore the diatribe that followed in light of your misunderstanding. (And the fact that you share my mother's maiden name, so may well be long distant family...). I would agree that there are many places that the wizards in Access and the sample databases encourage sub-optimal practices. But most professional developers aren't using either wizards or the sample databases as their models for developing their own applications. If your impression of Access comes from futzing with it for 10 minutes and from encountering kludged-together apps created by your company receptionist, then you just haven't a clue what Access offers, either as an application development platform or as a data store (using its native Jet engine). This is a schema question, and that is orthogonal to Access, because Access is an application development platform. If you use Jet for your data store, then Jet is relevant to the discussion. But you can use any data store that offers an ISAM or ADO or ODBC drivers, and that means all your schema issues are completely divorced from Access itself. That you can't seem to keep this distinction clear in your mentions of Access demonstrates pretty clearly that you are completely clueless about Access Well, apart from this one. Up yer bum. That's a term of endearment in the UK (from whence your name originates), of course and really aren't in any position to be making disparaging comments about it. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#114
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 27, 6:16 pm, Marshall wrote:
On Jan 26, 6:09 pm, "James A. Fortune" wrote: Marshall wrote: On Jan 26, 4:26 am, "David Cressey" wrote: When you want to delete an entry form a junction table, you almost always know the two FKs that uniquely determine the entry to be deleted. You almost never know the value of the superflous surrogate key. So it's simple to use the two FK's as the criterion for deletion than it is to look up the ID field, and then use that as the basis for deletion. Yes, exactly. One of the greatest benefits, and one of the fundamental differences between how SQL treats data and how (most) conventional programming languages treat data is that in SQL we specify data by its value, instead of by location. I often observe that superfluous keys in the field are an attempt to make SQL data have an address, to make it behave the way the programmer's mental model (perhaps influenced by years of using pointers) does. Personally, I don't take the natural keys out either, so they can still be used for the deletion. Since natural keys are data that needs to be managed, this is an unsurprising claim. I don't throw out the data I'm supposed to be managing either. Also consider this thread is about junction tables. Imagine what you would have left if you threw out the natural key of a junction table: nothing! Just the capriciously introduced surrogate key in a table by its lonesome self. Perhaps I've just stumbled into a sanity check for table design: every projection of a table should have a meaningful predicate. (Do I hear JOG's ears pricking up?) I have no idea what you mean. Well not since my semantic-2000 buzzword detector imploded when someone described "web 3.0" to me. In a junction table in which a surrogate key has been introduced, what's the meaning of the projection of the table over the s.k. column? Oops! The thought of giving the SQL data an address and following a programmer's mental model did not enter into my thinking at all. Your not being consciously aware of it doesn't mean it didn't happen. The mind doesn't work like that. You've been listening to Celko too much. Who? ;-) Marshall |
#115
|
|||
|
|||
Separate PK in Jxn Tbl?
Jamie Collins wrote:
I'm saddened, although not surprised, at the comments from a few regulars from the c.d.theory newsgroup using words such as idiot "Clearly the person is an idiot and is better off employed at a 7-11." http://groups.google.com/group/micro...3c373772d787d3 "One idiot student once emailed me" http://groups.google.com/group/micro...5e152c11a9e0a3 "tell him he's an idiot" http://groups.google.com/group/micro...c0abc7b367656d "This person is an idiot." http://groups.google.com/group/micro...edf72a4680cf25 "But no, some idiot manager at Mickeysoft..." http://groups.google.com/group/micro...2d3ed992e9a2ec chuckle You almost got me there. However I was ridiculuing other people who weren't part of the conversation. Not much of an excuse. 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/ |
#116
|
|||
|
|||
Separate PK in Jxn Tbl?
"Brian Selzer" wrote:
Only an idiot would have a rule for no particularly good reason. Only an imbecile would follow such a rule. A strong argument can be made for using autonumber primary keys--especially if the target DBMS doesn't support FOR EACH ROW triggers--but to just blythely add them for no particularly good reason is a recipe for disaster. My reasons are, in my opinion, good reasons. Not great but good. You don't like them? Tough. So now they're good reasons? In your earlier post, you said they weren't good reasons. Can't you make up your mind? You also haven't stated your reasons. How can I like them or not like them? I don't know them! They are good reasons for me and, in my opinion, for many or all other users of Access. I flippantly stated "no particular good reason" as I was thinking about some of the regulars in the comp.databases.theor newsgroup and what I assumed would be there viewpoint. I was not speaking of corruption due to disk failures; I was instead referring to permitting garbage into the database due to the misuse of auto-number primary keys. Ok, not quite sure what you mean by this. Presumably because a unique index wasn't declared on other fields such as, for a parts table, a part number. Which can be duplicated by multiple manufacturers so even there that's not quite a good example. 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/ |
#117
|
|||
|
|||
Separate PK in Jxn Tbl?
-CELKO- wrote:
I always use an autonumber PK and a unique index set on the two FK fields [sic]. Why? No particular good reason. One of my database rules is that all tables have an autonumber primary key [sic]. You add redundancy to a schema and never thought about it? I have and I'm quite comfortable with it. If you had gone thru the Normalization process, this would stick out as a fundamental design error immediately. You have not been writing SQL; you are faking a sequential file system in SQL and even say "field" instead of "column" -- huge conceptual and implementation differences! Ok. Now if I was to have a child table [sic: that terms comes from Network DBs; did you used to program in IMS?] from the junction table [sic: did you mean a table that models a relationship among many entities? Or a multi-way pointer structure as in an Network DB?] then I would absolutely use a autonumber primary key [sic: it cannot be a key by definition] for ease of use when designing queries, forms and reports. Never woked in IMS or Network DBs. These are common terminology in the Access world so that's what I use. I hope that you are not designing forms and reports in the database. You've never used Access then? 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/ |
#118
|
|||
|
|||
Separate PK in Jxn Tbl?
rkc wrote:
Yes. Because Access developers have no need to separate data access from user interface from business rules. Just bind a form to a recordsource, sprinkle some VBA code in a few events and run with it. I sure would like to have that middle tier with business rules and also have the power of Access and RAD. 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/ |
#119
|
|||
|
|||
Separate PK in Jxn Tbl?
"James A. Fortune" wrote:
I am only speaking for myself. I may be the only Access programmer on the planet who validates input the way I do in code. Not sure exactly what mean mean by that statement but I do a lot of validating on input as well. And I very much try to keep one form updating one table so as to not have to duplicate such data. I'd very much like to see a business rules layer implemented that worked well within Access. However I rather much doubt we'll ever see it given that Microsoft is focusing their attentions on the office information worker rather than the developer. 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/ |
#120
|
|||
|
|||
Separate PK in Jxn Tbl?
Quoth David W. Fenton:
Any database engine can have the schema defined in a way that will allow duplicates. Nonsense. Any /SQL-based/ database engine, perhaps. -- Jon |
Thread Tools | |
Display Modes | |
|
|