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
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 25, 2:23 am, "Tony Toews [MVP]" wrote:
JOG wrote: I was tempted to simply reply: "avoiding using Access is just a rule I have. Why? No good reason. The access fanboys will argue. I don't care" ...but, hey that sort of sentiment would surely just be peurile, and discourage useful discussion would it not? BTW I didn't realize that there were people in the theory newsgroup who actually wanted to discuss the issues in a reasonable fashion. Then consider yourself corrected One person's posting style does not a news group make (and FWIW if you can get past that posting style there is a lot of value to be had imo). I had a look at your own website and saw the effort you have put in to help other people, so kudos for that. But this is partly why I was so suprised at your "That's my rule, no good reason and if anyone disagrees sod 'em" post. I have given an example of where using an artifical key broke a database, and I'd stoutly argue that _hidden_ attributes are dangerous, period. I'm dubious as to how they simplify queries (they may shorten them, but not reduce their complexity as far as I can see), but I can also think of instances where they opposite would be the case. However, I am of course happy to be illuminated by some examples, if any access people want to convince me that using artificial keys on every table I create is a good thing... J. 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 athttp://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/ |
#52
|
|||
|
|||
Separate PK in Jxn Tbl?
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. -- |
#53
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 23, 12:45 am, "Neil" wrote:
I've done both; the separate primary key is (strictly speaking) never necessary, but it can be handy if the junction table is itself related one-to-many to an additional table or tables. Access doesn't make multifield foreign keys all that easy to use (e.g. you can't use them in a combobox without some messy code). Good to know. That makes sense, about needing the PK to refer separately to the junction table, if that situation exists. Why do you say "needing the PK"? JohnV no doubt makes a good case when considering bound controls in Access but I would point out that he also said the PK is "never necessary, but it can be handy". Jamie. -- |
#54
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 25, 1:13 am, "Tony Toews [MVP]" 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 Jamie. -- |
#55
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 25, 12:59 pm, JOG wrote:
I had a look at your own website and saw the effort you have put in to help other people, so kudos for that. But this is partly why I was so suprised at your "That's my rule, no good reason and if anyone disagrees sod 'em" post. I too would like to pay tribute here to Tony Toews Access MVP, he does help a lot of people and does not deserve a hard time (same for Larry Linson, great guy, kind of the grandfather of this group, I like to think, though I still wonder what I did to offend him). I too read his website a while back and there's some good stuff in there and some links to some great chuckle-some comedy. Which leads me nicely... Sorry to spoil anyone's fund but am I the only one to spot the duplicitous (pun intended, natch) nature of this post and other similar ones recently i.e. where he *seems* to treat people he should care about (community regulars, newbies, his own clients, etc) with contempt? I think he knew he was lighting the blue touch paper of this thread by saying, "I don't care" and I might be partly responsible for this because I did kind of give him a good response to the same trick last week. Come on people, lighten up! Tony is being light-hearted, tickling your ribs. And I congratulate him for it, things can get dull around here Jamie. -- |
#56
|
|||
|
|||
Separate PK in Jxn Tbl?
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 |
#57
|
|||
|
|||
Separate PK in Jxn Tbl?
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 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 |
#58
|
|||
|
|||
Separate PK in Jxn Tbl?
"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 |
#59
|
|||
|
|||
Separate PK in Jxn Tbl?
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. What happens if something happens to the warehouse that makes it un-usable. Not enough to affect the items "in" the warehouse. Would this not mean the items are moved to another warhouse. Why not just use the item key as a stand alone or (as Sylvain suggested) have the Item Key as an autonumber. I'm not arguing either way - I am trying to learn better but it seems that createing a muliple layer primary field is just asking for problems when there is no need to do this as all DB's can cope perfectly well with just an autonumber. I was always told that the primary field was "not" there for an other purpose than to indetify that specific recordset. If you use multiple layered key fields are you not assigning another value to the primary (that of a - in your example - a product/item locator). As I said I'm not standing on either side I'm just wanting to me knowledge increase. Thank you -- Wayne Manchester, England. "Brian Selzer" wrote: "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 |
#60
|
|||
|
|||
Separate PK in Jxn Tbl?
"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.). I have decided not to respond to this post in detail because there isn't a single point it makes that I agree with (as stated). I really don't know where to start. One specific comment I will make is that my progression has been the opposite of yours. I once used synthetic keys everywhere, but now try to limit my use of them, with wholly beneficial effects. That may be why I write about this with the fervor of a born-again convert. I would have a lot more sympathy for these kinds of claims if the people making them would give any hint that they know what the alternatives are, and why their solutions make sense within application development tools. For example, I don't think I've ever seen anyone enthusing about the liberal use of synthetic keys who also noted that they are aware of the possibility of declaring a foreign key constraint WITH ON UPDATE CASCADE. If you can persuade me you've looked at it and had to reject for reasons X, Y, and Z, I can respect that. Or tell me you know about it but your particular product doesn't support it and I can respect that. Or tell me that you understand that the DBMS handles the problem almost trivially but the application development tools make you write extra code and I can respect that. And what is all this tripe about composite keys making the SQL more complex? If I bodge up my tables with a spurious third synthetic key (skey) so that instead of writing: select i.description, b.cause_of_damage from orderitems i left join breakages b using (ordernr,itemnr) I can instead write: select i.description, b.cause_of_damage from orderitems i left join breakages b on i.skey = b.skey How much easier is THAT?? And at what cost? If you don't convince me that you properly understand the problems, and crucially, where the problems *really* lie, then you aren't going to convince me that your solutions are anything but cut-and-paste rote-learned hackery that seems elegant/sensible only to those with limited knowledge of very limited products. You have to establish that you are credible. Merely claiming years and years of experience (as others have) could just mean they've been successfully getting away with being incompetent--and goodness knows, that happens, so they won't get the benefit of the doubt. Roy |
Thread Tools | |
Display Modes | |
|
|