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 |
#131
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 28, 2:03 pm, "David Cressey" wrote:
What if there's more than one application built on top of the database? I believe this is a moot point when it comes to MS Access. The app and the database are all stored together in Access. There is, by definition, only one Access. The properties of the Access user interface clearly need to be stored somewhere and the place is in an mdb (or equivalent) file. Jet table data is also persisted in mdb files. Yes, in a single user, traditional Access/Jet application, this is likely to be the same file as the Jet tables. However, in a multiuser Access/Jet application set up is most common to have the Forms, Reports, etc ('front end') 'split' from the 'back end; requiring a FE/BE split is one of the Access Ten Commandments (I kid you not). Typically, each user machine will have its own copy of a 'front end', therefore in such a situation there are indeed multiple applications accessing the same 'back end'. Jamie. -- |
#132
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 28, 3:10 am, "Tony Toews [MVP]" wrote:
chuckle You almost got me there. However I was ridiculuing other people who weren't part of the conversation. I acknowledge the fact I have nothing on you Not much of an excuse. Well, my quick search revealed you have called *yourself* an 'idiot' far more often and recently admonished a regular for name calling. You are a gentleman. Jamie. -- |
#133
|
|||
|
|||
Separate PK in Jxn Tbl?
"Roy Hann" wrote in message ... "David Cressey" wrote in message news:%9lnj.5893$cm6.4751@trndny05... When it is managed by other people, it is subject to mismanagement. Changing values that ought to be immutable is one of many ways that natural keys can be mismanaged. As I said before, I prefer to use natural keys where ever possible. If that's not possible due to mismanagement of the natural keys, I'll use synthetic keys. I think you may be asking too much of a natural key. I always took "stable" to mean *almost* never changes ratherer than meaning absolutely immutable (which I take to mean "intrinsically incapable of changing"). But whatever one thinks it should mean, one always has to accommodate the possibility that a key value will be mis-typed during manual entry by a user, and therefore that it will have to be corrected. Mismanagement is, as you rightly say, a fact of life. You are right. Whether or not mismanagement is a sufficient reason for distrust of a natural key depends on the degree of mismanagement. I should have made that explicit in my earlier post. But that's not usually a sufficiently good reason to introduce a synthetic key. I have found it possible to tolerate quite a lot of updates to supposedly stable keys using ON UPDATE CASCADE, with imperceptible response-time costs. (One can of course measure the costs, but that's not what counts.) A key would have to be really unstable to justify using a synthetic key IMO. One problem with cascading updates occurs when you want to relate data still in the database to data that was extracted from the database at an earlier point in time. This includes, but is not limited to, data in the log file. |
#134
|
|||
|
|||
Separate PK in Jxn Tbl?
"David Cressey" wrote in message
news:U6nnj.8$1f.5@trndny02... One problem with cascading updates occurs when you want to relate data still in the database to data that was extracted from the database at an earlier point in time. This includes, but is not limited to, data in the log file. Good points, and I will always concede that these are the kinds of external factors that in practice force one into using more synthetic keys than we'd like. Happily in my case the problem isn't too bad because we have a very nice journal analyzer that quickly reveals any key updates. I hope I have also been pretty careful to warn people that creating redundant copies of data outside the database will cause all the problems one always associates with redundant copies of data. We tend to do a lot of refreshes instead of hoping our snapshots haven't mutated too badly. (In a way it's been a chicken and egg solution: we never allowed the egg, so we don't have to deal with the chicken! :-) We also make use of replication in several systems, and to our replicator an updated key is just another update. But to repeat, I do understand synthetic keys will sometimes be forced on me. I don't insist they can always be avoided. They are my just last resort, not my first. Roy |
#135
|
|||
|
|||
Separate PK in Jxn Tbl?
"David Cressey" wrote in message news:%9lnj.5893$cm6.4751@trndny05... "Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote in message ... This is the big problem with natural keys. When the theory was first elaborated, the point was that a natural key never change its value. That was the point that was making the choice of a natural key on par with the use of a surrogate key as the primary key of a table. However, like anyone have discovered with experience, a natural key can change its value under a set of various circonstances. One could argue that if a key can change its value, than it's not a natural key but as you know, this argument bring nothing in regard to help you choosing a natural key. This is a specific example of a syndrome that I described more generally: the mismanagement of natural keys. If a key is "natural", it is managed, if at all, beyond the scope of the DBMS. It might be managed at the application layer, or it might be managed by people, or it might indeed be unmanaged data, like sunspots. When it is managed by other people, it is subject to mismanagement. Changing values that ought to be immutable is one of many ways that natural keys can be mismanaged. As I said before, I prefer to use natural keys where ever possible. If that's not possible due to mismanagement of the natural keys, I'll use synthetic keys. I don't think it's productive to blame it on the users. The problem you're referring to is due to the nature of keys, and misunderstandings on the part of the database designer as to what constitutes a key. All that is required for a key to be a key is that in every possible database instance, a projection over the attributes in the key for a relation has the same cardinality as the relation. This does /NOT/ mean that a particular combination of values /always/ identifies the same individual in the Universe of Discourse, but only in the picture of the Universe that is a database instance. In other words, a particular combination of values may not /necessarily/ identify an individual, but rather may only /contingently/ identify an individual. This is the nature of keys: either the values for a key are permanent identifiers, or they're not. It has nothing to do with how well keys are managed. The values for a key may be managed perfectly, yet still not be permanent identifiers--the position of something in a list of things comes to mind. |
#136
|
|||
|
|||
Separate PK in Jxn Tbl?
Bof, if you live in a perfect world, one with infinite budget and infinite
time to do any project, then I understand your concerns. However, in my case and probably in the case of Tony Toews - but I cannot vouch for sur for him - I don't live in such a world. First of all, if I were to live in a perfect world, I wouldn't have to work to earn a living in the first place. Second, you cannot codifying everything for a variety of reasons: budget, system already in place and working well, impossibility to anticipate everything, more art than a science, etc., etc. I have a client who has such a system at the moment as one critical part of their business process, this system is working well at the moment and has done so for many years; it would probably make your teeth gnashing but it's not on their radar at this moment to change it. Why would they pay to change something that had worked well for them for many years and at the risk of finding themselves at the front of something new that might not work as well as the one system? To give pleasure to people like Celko? If I were to tell them that Celko would like to see them changing their system, they would probably tell that if Celko was to bring them a check to pay for the change, then maybe they would give it a try. In this message, you can replace the name of Celko with the name of a lot of persons around here but not with mine. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Jamie Collins" wrote in message ... On Jan 27, 9:59 pm, "Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please) wrote: « But many of them seem to write as if contents as determined by ddress ». The content is not determined by the address and in fact, for those who are using surrogate keys, the exact value of an address inside the database has zero importance. For you maybe. In a recent thread on this subject, Tony Toews Access MVP qualified that he liked using incremental autonumbers (rather than random) because they where easier to type (WHERE ID = -2001736589 may encourage typos) and easier to drop into conversation ("Hello Tony? I'm seeing a problem with the record where the ID is -2001736589..."). Did I mention that I sincerely appreciate Tony's honesty? Also consider the amount of posts we see in the Access groups asking to reseeding autonumbers, gaps in sequences, etc. Wrong mental model, perhaps, but the mentality certainly exists. Jamie. -- |
#137
|
|||
|
|||
Separate PK in Jxn Tbl?
Jamie Collins wrote:
chuckle You almost got me there. However I was ridiculuing other people who weren't part of the conversation. I acknowledge the fact I have nothing on you Not much of an excuse. Well, my quick search revealed you have called *yourself* an 'idiot' far more often and recently admonished a regular for name calling. You are a gentleman. Ah, thanks. I had actually forgotten all that. 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/ |
#138
|
|||
|
|||
Separate PK in Jxn Tbl?
Bob Badour wrote:
I believe this is a moot point when it comes to MS Access. The app and the database are all stored together in Access. There is, by definition, only one Access. Access regulars, feel free to correct this if it's wrong. While awkward, slow and kludgy, it is possible to attach an Access app to an external database. And how is Access more awkward, slow and kludgy than other products such as say VB 6.0, VB.Net, or whatever? 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/ |
#139
|
|||
|
|||
Separate PK in Jxn Tbl?
Jamie Collins wrote:
On Jan 28, 3:38 am, "Tony Toews [MVP]" wrote: 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. I saw your earlier post on this and wondered if you were being genuine. Now that I see you are... Seriously, have you tried doing this kind of thing in Visual Studio 2005 i.e. using the .NET framework? I was helping a work colleague (non-dev) recently who was using the free version of VS to do a simple club subscriptions app, with data bound controls, navigation bar, etc i.e. at a glance very reminiscent of Access Forms (except the look and feel was much nicer g). I've also made initial investigations into third party frameworks (i.e. extending .NET framework) to bind business rules' logic to controls. If you believe that MS are not evolving Access in the developer's interests (and I think you may well be correct) and at the same time are making .NET Windows Forms development more accessible (pun intended), maybe it's time to re- evaluate your personal direction... It's my understanding that the ease of use and speed of development of the latest ..Net products is getting closer to that of Access. So I'm certainly keeping my eye on these. Not convinced yet but then I haven't done next to no research in this area. Too busy. smile 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/ |
#140
|
|||
|
|||
Separate PK in Jxn Tbl?
"Brian Selzer" wrote in message
news I don't think it's productive to blame it on the users. The problem you're referring to is due to the nature of keys, and misunderstandings on the part of the database designer as to what constitutes a key. All that is required for a key to be a key is that in every possible database instance, a projection over the attributes in the key for a relation has the same cardinality as the relation. This does /NOT/ mean that a particular combination of values /always/ identifies the same individual in the Universe of Discourse, but only in the picture of the Universe that is a database instance. In other words, a particular combination of values may not /necessarily/ identify an individual, but rather may only /contingently/ identify an individual. This is the nature of keys: either the values for a key are permanent identifiers, or they're not. It has nothing to do with how well keys are managed. The values for a key may be managed perfectly, yet still not be permanent identifiers--the position of something in a list of things comes to mind. No, the position number identifies the same position, for all time. The facts about the current occupant of the position may change willy-nilly. Roy |
Thread Tools | |
Display Modes | |
|
|