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 |
#211
|
|||
|
|||
Separate PK in Jxn Tbl?
"James A. Fortune" wrote in message ... David Cressey wrote: database, is qualitatively different from the design target of the people who write Access databases and applications. If they ever get to the point where the complexity of what they are doing matches the complexity of what practitioners using SQL Server, Oracle, or DB2 are doing, or the complexity that database theorists are addressing, they will be forced to either learn or disprove what some of us know, or think we know. I don't have broad enough experience to dispute your argument. I understand that people who specialize in SQL and deal with more complex situations than most develop practices that make use of their more intimate knowledge of SQL. However, I can't just take their word about their decisions. I have to understand how those choices apply to what I'm doing. I agree, absolutely. What I was arguing against was the dismissal without evaluation of what theorists have to offer. Without making light of their potential contribution, I avoid the specious argument that because a large company or IT department does things a certain way or spends more money on the problem makes their solution inherently correct. Plus, the complexity of the problems they face often argue against their use in Access. Few Access developers have the luxury to hire or supervise a full-time SQL developer. If using multiple field natural keys causes a problem(s), a full-time SQL developer has time to work with the SQL until the problem is solved. SQL is only part of our job. SQL isn't the silver bullet, either. Maybe many Access programmers prefer a single key to limit the number of fields that get corrupted :-). In that case, I believe they are wrong. |
#212
|
|||
|
|||
Separate PK in Jxn Tbl?
"Roy Hann" wrote in message ... "Brian Selzer" wrote in message news Constraints should always be checked by the DBMS, not by applications. I agree very heartily with the first part of this statement, for the reasons you give below. I disagree with the second part (as stated). There is no reason why applications shouldn't also test what constraints they can. The problem is that they should not have hand-coded re-implementations of the constraints because those will get out of sync with the database over time. What would be very nice is if one day it were possible for applications to download the relevant constraints at run-time, the way they presently download other metda-data. That's a good point. I should have said instead, "Constraints should always be /enforced/ by the DBMS, not by applications." It is often a good thing for an application to do some checking because it can reduce the number of round-trips, and maybe even some transaction rollbacks. If you have two separate applications that manipulate the same table, and one enforces one constraint while another enforces another, then all you need to do to bypass one constraint is to use the other application! What, then, is the point of even having the constraint? Roy |
#213
|
|||
|
|||
Separate PK in Jxn Tbl?
"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... In my opinion, the only sound reason for using artificial keys is when the values of a natural key do not permanently identify individuals in the Universe of Discourse--that is, the micro-world that the database is supposed to be a model of--and when there is a demonstrable requirement for permanent identification. Any other use adds complexity for complexities sake--in other words, for no particularly good reason. Even in the very few cases were you think that a natural key will be a true natural key, ie. it will be permanent and totally managed at 100% (which mean no data entry error of any kind, no change of value because of identity theft, etc., etc.); the privacy issues will forbid their use in many cases. Many countries now forbid by law to ask for something such as your social security number when you don't need it and when you need it, to use it as a key instead of storing it confidentially (ie., by encrypting it). So, where does it say that there can only be one natural key on a table? Even MS had problem with this in the past: the first version of the algorithm for generating a GUIG was using the NIC number as part of its algorithm. They had to remove it later and now, the generation of any GUIG is totally random. The older algorithm is still available in Windows under another name but MS warns that anyone using it might be doing so illegally under the laws of their countries. The same argument apply to things like RFID: how many of you would like to see big corporation to know which medicaments you are bringing home or your spouse to know that you have just bought a box of condom? Why should it be a problem for my spouse to know that I just bought a box of condoms? More often than not she is the one who lets me know that we need them. Oh! Wait! I get it! That must be something those of you who can't keep it in your pants worry about. Using natural keys is like building a castle of cards: they might look impressive but it doesn't take to much to bring them down. Even with artificial keys, you still need natural ones. Without them, you can have multiple values for a key that mean exactly the same thing, but with no way to determine which, thereby rendering the key useless. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Brian Selzer" wrote in message news "James A. Fortune" wrote in message ... David Cressey wrote: database, is qualitatively different from the design target of the people who write Access databases and applications. If they ever get to the point where the complexity of what they are doing matches the complexity of what practitioners using SQL Server, Oracle, or DB2 are doing, or the complexity that database theorists are addressing, they will be forced to either learn or disprove what some of us know, or think we know. I don't have broad enough experience to dispute your argument. I understand that people who specialize in SQL and deal with more complex situations than most develop practices that make use of their more intimate knowledge of SQL. However, I can't just take their word about their decisions. I have to understand how those choices apply to what I'm doing. Without making light of their potential contribution, I avoid the specious argument that because a large company or IT department does things a certain way or spends more money on the problem makes their solution inherently correct. Plus, the complexity of the problems they face often argue against their use in Access. Few Access developers have the luxury to hire or supervise a full-time SQL developer. If using multiple field natural keys causes a problem(s), a full-time SQL developer has time to work with the SQL until the problem is solved. SQL is only part of our job. It has been nice to see posters in microsoft.public.access such as Jamie Collins and Ken Sheridan, who seem to have a lot of standard SQL experience, branch off into other issues that Access programmers face. I think their understanding of those issues can help us differentiate between purely SQL issues and Microsoft implementation issues. We all agree that Microsoft has made questionable design decisions in Access, but the possibility of inclusion of unbound forms or of AutoNumber primary keys might not be part of that list, as some have suggested. I have enough experience to say that using unbound forms in Access and using artificial keys did not cause any problems when scaling an Access application up to an ASP solution using SQL Server. For something more complicated perhaps there are subtle issues that arise that merit our attention. Table level constraints also raise an issue. At the table level, there is no VBA code run to ensure that any constraints (e.g., on the natural keys themselves) are enforced. Thus, a set of natural keys is sufficient to specify the key constraint. Any artificial key becomes superfluous. The possibility of separate applications using the same table, brought up by David Fenton, brings up the interesting possibility that the applications have separate, possibly disparate constraints. In disparate constraint situations not all of the constraints can be at the table level, although they can be specified at the query level in each application rather than via code. Constraints should always be checked by the DBMS, not by applications. If you have two separate applications that manipulate the same table, and one enforces one constraint while another enforces another, then all you need to do to bypass one constraint is to use the other application! What, then, is the point of even having the constraint? Maybe many Access programmers prefer a single key to limit the number of fields that get corrupted :-). Perhaps an enumeration of sticky situations encountered by those using multiple field natural keys would help us understand why some avoid them. Maybe better solutions to those sticky problems would encourage developers to stay with natural keys more often. In my opinion, the only sound reason for using artificial keys is when the values of a natural key do not permanently identify individuals in the Universe of Discourse--that is, the micro-world that the database is supposed to be a model of--and when there is a demonstrable requirement for permanent identification. Any other use adds complexity for complexities sake--in other words, for no particularly good reason. James A. Fortune |
#214
|
|||
|
|||
Separate PK in Jxn Tbl?
Quoth Roy Hann:
What would be very nice is if one day it were possible for applications to download the relevant constraints at run-time, the way they presently download other metda-data. Dataphor does this. -- Jon |
#215
|
|||
|
|||
Separate PK in Jxn Tbl?
"Jon Heggland" wrote in message
... Quoth Roy Hann: What would be very nice is if one day it were possible for applications to download the relevant constraints at run-time, the way they presently download other metda-data. Dataphor does this. You don't think I was clever enough to come up with the idea all by myself do you? :-) I admire what Dataphor set out to do. But having been forced to adopt SQL against my will a long time ago, I am under no illusion that anything better than SQL will ever catch on. The best hope is that embedded SQL might be less stupid in future. Roy |
#216
|
|||
|
|||
Separate PK in Jxn Tbl?
Quoth Roy Hann:
"Jon Heggland" wrote in message ... Quoth Roy Hann: What would be very nice is if one day it were possible for applications to download the relevant constraints at run-time, the way they presently download other metda-data. Dataphor does this. You don't think I was clever enough to come up with the idea all by myself do you? :-) Why not? It's a pretty simple idea, really. I admire what Dataphor set out to do. But having been forced to adopt SQL against my will a long time ago, I am under no illusion that anything better than SQL will ever catch on. Well, Dataphor databases are typically built on top of SQL databases. Just tell management that Dataphor is really just a presentation layer on top of SQL. The best hope is that embedded SQL might be less stupid in future. If that was my best hope, i think I would quit being a database engineer. -- Jon |
#217
|
|||
|
|||
Separate PK in Jxn Tbl?
"Jon Heggland" wrote in message
... Quoth Roy Hann: The best hope is that embedded SQL might be less stupid in future. If that was my best hope, i think I would quit being a database engineer. I admire your optimism. Can we agree to meet again in this very place 20 years from now to discuss how it went? :-) Roy |
#218
|
|||
|
|||
Separate PK in Jxn Tbl?
"Brian Selzer" wrote in message news "Roy Hann" wrote in message ... "Brian Selzer" wrote in message news Constraints should always be checked by the DBMS, not by applications. I agree very heartily with the first part of this statement, for the reasons you give below. I disagree with the second part (as stated). There is no reason why applications shouldn't also test what constraints they can. The problem is that they should not have hand-coded re-implementations of the constraints because those will get out of sync with the database over time. What would be very nice is if one day it were possible for applications to download the relevant constraints at run-time, the way they presently download other metda-data. That's a good point. I should have said instead, "Constraints should always be /enforced/ by the DBMS, not by applications." It is often a good thing for an application to do some checking because it can reduce the number of round-trips, and maybe even some transaction rollbacks. I think that if an application does some checking, it will also do some enforcing. I think you might have been aiming at something like the following: The DBMS should always enforce the constraints that it can enforce, rather than relying on applications to refrain from writing data that violates the constraints. Note that the above is silent on what applications should or should not do regarding constraints. |
#219
|
|||
|
|||
Separate PK in Jxn Tbl?
-CELKO- wrote:
Thank you. You made my point. I was only wrong on the non-English speaking programmers. It doesn't appear to be a famous failure, more like a internal problem exacerbated by management. It is a good classic screw up, with blame for everyone! 1) ACCESS programmer builds desktop app on his own that looks good for his immediate needs. Obviously a dedicated person that takes some initiative. 2) Management sees the app and wants to deploy it all over the company. Hey, why design anything new when we have it already? Dilbert's pointy headed boss comes to the rescue. 3) ACCESS programmer claims it will deploy and and management believes him. The programmer is correct. 4) It does not scale, it does not interface with mainframe apps, external apps, etc. It has no documentation, etc. Management decides unwisely to not spend money to upgrade it or redefine it to meet goals. Programmer has a life and a job and doesn't have time to write documentation. Mgt is too cheap to provide him with a technical writer or someone to do bug testing. This is obviously not an important project. 5) ACCESS programmer now has a career being the only guy who can keep the sinking boat up. Never mind how many times a week it has to be re- booted or how much data is lost. Programmer skill level may come into play. Feature creep may come into play. Management thought on project is nil. Something for nothing and the chicks for free. 6) Neither the programmer nor management will scream for help and ask for a budget. Management would look stupid; programmer would lose his job and power You get what you pay for. I can't fault the programmer. He made something to make his job easier. Mgt glommed onto it but wasn't willing to invest in it. I asked before, are you on the committee that oversees the project? If so, look in the mirror. |
#220
|
|||
|
|||
Separate PK in Jxn Tbl?
"David Cressey" wrote in message news:wOFoj.5442$4f.4907@trndny06... "Brian Selzer" wrote in message news "Roy Hann" wrote in message ... "Brian Selzer" wrote in message news Constraints should always be checked by the DBMS, not by applications. I agree very heartily with the first part of this statement, for the reasons you give below. I disagree with the second part (as stated). There is no reason why applications shouldn't also test what constraints they can. The problem is that they should not have hand-coded re-implementations of the constraints because those will get out of sync with the database over time. What would be very nice is if one day it were possible for applications to download the relevant constraints at run-time, the way they presently download other metda-data. That's a good point. I should have said instead, "Constraints should always be /enforced/ by the DBMS, not by applications." It is often a good thing for an application to do some checking because it can reduce the number of round-trips, and maybe even some transaction rollbacks. I think that if an application does some checking, it will also do some enforcing. I think you might have been aiming at something like the following: The DBMS should always enforce the constraints that it can enforce, rather than relying on applications to refrain from writing data that violates the constraints. Note that the above is silent on what applications should or should not do regarding constraints. Yes. |
Thread Tools | |
Display Modes | |
|
|