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 |
#121
|
|||
|
|||
Separate PK in Jxn Tbl?
On Jan 27, 6:30 pm, Marshall wrote:
There's another approach to enforcing constraints: just tell your users not to do that. In the hierarchy of what works, it comes below checks in client code, but above manually inserting garbage at design-time. It also has the advantage of being even lower performance overhead for the software. Funny but I've actually seen this in the Access groups as an answer to the alluded to question, "How do you prevent data corruption to your 'Access' data by users connecting via Excel?" (a common enough scenario) i.e. put it in the staff handbook and make doing so a disciplinary matter. Yes, I know: door...horse...bolted. Jamie. -- |
#122
|
|||
|
|||
Separate PK in Jxn Tbl?
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... Jamie. -- |
#123
|
|||
|
|||
Separate PK in Jxn Tbl?
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 address ». 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. -- |
#124
|
|||
|
|||
Separate PK in Jxn Tbl?
"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. |
#125
|
|||
|
|||
Separate PK in Jxn Tbl?
"Rob" wrote in message ... On Jan 22, 1:26 pm, "Neil" wrote: (quote) I don't claim to know more about db theory than the cdt regulars, or to have more experience than the many practioners who have contributed to this thread. I do know that the debate over relationship representations has been going on for years, neither side giving any ground. Some aspects of it are never addressed. For example, making the two foreign keys in a junction table a composite PRIMARY key prohibits NULL values from either key, but declaring a separate surrogate primary key together with a UNIQUE constraint on the two-foreign-key-composite-key does allow NULL values for either (or both!). This can be useful for representing unrelated entity tuples, either childless parents or orphans. (unquote) In the case of a junction table, this point is moot. If both FK values are NULL, the entire row of the junction table can be omitted with no loss of information. Childless parents will exist in the table that contains parents, where the id of the parent is not an FK, but a PK. Same pattern for orphans. The two tables can even be the same table. |
#126
|
|||
|
|||
Separate PK in Jxn Tbl?
"David W. Fenton" wrote in message .89... wrote in m: 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? 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. Access regulars, feel free to correct this if it's wrong. |
#127
|
|||
|
|||
Separate PK in Jxn Tbl?
"-CELKO-" wrote in message ... Does a bottle of Gin have a serial no? I am too poor to buy 2 at a time to see if there is a different number Ah! In the US, you get a UPC/EAN code and then a batch number with booze. The closest thing to a serial number is your cash register receipt. This is the "cat food problem" in a nutshell. (Or, in this discussion, the "gin bottle" problem.) The problem has been known as the "cat food" problem for years and years. Items on a cash register tape represent entities with no identity at the individual level, only at the batch or product level. You therefore can't tell whether two bottles of gin were checked out, or whether one bottle was scanned twice. |
#128
|
|||
|
|||
Separate PK in Jxn Tbl?
"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. 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. Roy |
#129
|
|||
|
|||
Separate PK in Jxn Tbl?
"David Cressey" wrote in message
news:fhlnj.2005$k1.588@trndny02... "David W. Fenton" wrote in message .89... wrote in m: 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? 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. Access regulars, feel free to correct this if it's wrong. The app and the data *can* be stored in one MDB, but do not *have* to be stored in one MDB (linked tables). It is entirely possible - and in my experience not unusual - to have more than one application reading and or updating data in an MDB (or ACCDB). -- Brendan Reynolds |
#130
|
|||
|
|||
Separate PK in Jxn Tbl?
David Cressey wrote:
"David W. Fenton" wrote in message .89... wrote in m: 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? 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. 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. |
Thread Tools | |
Display Modes | |
|
|