Bill Benson
bensonforums at gmail.com
Sat Apr 12 23:08:40 CDT 2014
Hi Jon, Not a safe question really, there are several answers. This article is a pretty good one. http://www.agiledata.org/essays/keys.html The rest of this post is anecdotal. There comes a time in a lot of data tables' lives, when their records will need to be ported over to a new system, and the column values will not really fit in the new schema. Such records have to be there in order to have a complete picture of the data, but they might not fit the new data rules, and therefore the FKs have to be relaxed while someone chips away at the data. DBAs don't like unioning "good data" with bad, from what I have seen, so they let it all be "bad" (ie, take away the constraints) while cleanup is occurring in batches on the back-end through update queries. What happened in one system at GE was a control was bound to a column in a table (or a view, doesn't matter) and if the user did nothing, the field displayed displayed a certain value (let's say a commodity) as it had been entered. So there was a commodity code and a commodity value, right... in the record - it was not handled through a lookup, because the table needed to show obsolete commodities, since they really existed in the non-cleaned-up data. So, as long as the user just looked at the record, no problem. But the second they clicked on the drop down (whose job it was to allow the user to edit the commodity if need be, of course that control looked up to a current commodity table. Naturally, the old code and value were not found, so the control would not show what had been on the record. And therefore, the user was left with no choice but to select something - which changed the record permanently (if they did not escape out in time). And because this stupid had some kind of autosave going on, often their "choice" permanently over-wrote the bad data. Now, some purists would say that the data should not have been introduced to the user which was not compliant with the new commodities, but I kid you not, some of these commodities were freely entered in the old system and so hard to figure out, that no automated system with any amount of fuzzy logic could have fixed them. Yet they were on actual shipments, the company could not abandon the records. So they had to relax the constraints and let the data in. but the query populating the control candidate values did not include the old value (see, there is where I think the mistake lay, it should have unioned the existing value with the other "good" choices). This does not help you answer your dilemma, but I felt like relating it, ha haha... I believe this kind of stuff happens a lot. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jon Albright Sent: Saturday, April 12, 2014 11:12 PM To: accessd at databaseadvisors.com Subject: [AccessD] Many To Many Issue Hey Bill, Hopefully this isn't a duplicate post. Haven't been able to reply to your earlier message. So when creating the the cross tables, is it supposed to be a composite key between the two FK's of the related tables or can you have a primary key consisting of an autonumber field along with the two FK's ? Autonumber FK Number (long int) FK Autonumber PK Number (long int) FK Number (long int) FK I have seen examples of it used both ways and one states you must use a composite key? Of course you can't always believe everything read on the internet... Jon Albright Iniki Enterprises Honolulu, Hawaii (808) 677-7800 x116 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com