 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Replication / July 2005Autonum field for Relationships and Replication
David and Cheval, Sorry not to reply within the original thread but my OE is playing up and will only send a 'new' message. Hate to brake protocol and will probably have to reinstall windows. Thanks for the blow by blow instructions. I am slowly running the queries to update all the related tables. It seems to work very well. As the replicas are installed on only 4 laptops I think I will just make the changes to the DM and then delete and add new replicas on the laptops. I'll continue to digest your instructions. There will always only be one autonumber field in any table and random numbers are fine as long as related fields in tables are linked by the 'same' random number. Davids query seems to have ensured that. All the autonumber fields are reference only for the database to link tables and uniqueness. Cheval, Your point about sequential number fields is very interesting, my CandidateID field would ideally be say 450 then 451 and so on but seems impracticle in replicated environment. Using a replica code seems an alternative but by law our CandidateIDs have to be contiguously incremented integers. How would an ID number be built using the date time field and how would it fit into the scheme of all the ID numbers once synchronized with the DM? Regards, Penny Hi Penny. That's good it's settled. Also try reinstalling OE first before Windows. As far as having the CandidateID's being sequential in a replicated environment, I've come across this before, and a solution was to use the "request and append" method. In basics; a replica creates the data and puts it in a temp table as a request to have it appended into the real table(s) at a later time. The data is then replicated to the append replica, which transfers the temp data to the real tables. That way only one replica appends the records and therefore can guarantee that the ID numbers will be sequential. An additional advantage is that you can then set the real tables to read only to all users (except the append user of course) and have no worries of data being edited or deleted once created. This is an excellent bonus when legal issues have to be taken into account. David and Cheval, Sorry not to reply within the original thread but my OE is playing up and will only send a 'new' message. Hate to brake protocol and will probably have to reinstall windows. Thanks for the blow by blow instructions. I am slowly running the queries to update all the related tables. It seems to work very well. As the replicas are installed on only 4 laptops I think I will just make the changes to the DM and then delete and add new replicas on the laptops. I'll continue to digest your instructions. There will always only be one autonumber field in any table and random numbers are fine as long as related fields in tables are linked by the 'same' random number. Davids query seems to have ensured that. All the autonumber fields are reference only for the database to link tables and uniqueness. Cheval, Your point about sequential number fields is very interesting, my CandidateID field would ideally be say 450 then 451 and so on but seems impracticle in replicated environment. Using a replica code seems an alternative but by law our CandidateIDs have to be contiguously incremented integers. How would an ID number be built using the date time field and how would it fit into the scheme of all the ID numbers once synchronized with the DM? Regards, Penny Cheval, I understand sequential ID numbers are a problem in a replicated environment so I'm resolved for now to add and use an autonumber for all main tables. I will retain the existing incremented ID fields but make the new ID field the pk. Thanks for your tips on the "request and append method". I'll take a bit of time to digest the idea and if I think I can pull it off I'll practice using that process on the non pk ID field first. How does one reinstall OE? Regards, Penny. > Hi Penny. That's good it's settled. Also try reinstalling OE first before > Windows. [quoted text clipped - 42 lines] > > Penny > I understand sequential ID numbers are a problem in a replicated > environment so I'm resolved for now to add and use an autonumber [quoted text clipped - 4 lines] > a bit of time to digest the idea and if I think I can pull it off > I'll practice using that process on the non pk ID field first. I think you're actually missing an important point here. Once you convert to an Autonumber PK, you still have to deal with possible collisions on your derived sequential key, if you're still using it. If you're not, then there's nothing to worry about. But if you still need to maintain a sequential numbering system, you have to address the very real fact of collisions between replicas, and then you either have to adjust the numbering systems to actually be independent in each replica, or you have to have one of the replicas be the sole numbering authority.  SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc It's funny, when designing databases I always (except for lookup tables) put a autonumber field as the PK in every table and create the constraints in code. That way I'm never tempted to use it as real data and get into a situation like you are in. But in your case, for all the work/risks involved, and also especially since the CandidateID field needs to be sequential, I would be putting my effort into working out something like "Request and Append" method. Taking a step back and looking at it objectively, lets see, your design is properly SQL normalised and works fine now. You want to make it replicatable. The problem is you want to keep the CandidateID sequential in the replicated environment. Right? Option A: Add a new autonumber PK to every table. Work? Lots. Plus you also now have to create the table constraints in code. Risks? High. Too many to list, and when do you update the front end for the new design? You have to kick the users out until your finished. Fix problem? No. Replica A can still create new records, incrementing the CandidateID number at the same time as Replica B. Therefore creating duplicates. Option B: Leave the fields alone and create a design that allows only one replica to add records. Work? Moderate. At least you don't have to re-test the whole application, just the new part. Risks? Very low. You can create the new structure, test and when complete, update the front end to use it. No downtime for the users. Fix problem? Yes. The CandidateID is again guaranteed to be sequential. Anyway, it's your choice and I hope it works out well for you. :) Outlook Express: http://www.google.com.au/search?q=reinstall+outlook+express&sourceid=mozilla-sea rch&start=0&start=0&ie=utf-8&oe=utf-8&client=firefox-a&rls=org.mozilla:en-US:off icial Cheval, I understand sequential ID numbers are a problem in a replicated environment so I'm resolved for now to add and use an autonumber for all main tables. I will retain the existing incremented ID fields but make the new ID field the pk. Thanks for your tips on the "request and append method". I'll take a bit of time to digest the idea and if I think I can pull it off I'll practice using that process on the non pk ID field first. How does one reinstall OE? Regards, Penny. "Cheval" wrote: > Hi Penny. That's good it's settled. Also try reinstalling OE first before > Windows. [quoted text clipped - 42 lines] > > Penny Cheval and David Thanks for your further replies. I am going with adding the Autonumber to all pertinent tables(which I will ALWAYS do in future) and yes that will be alot of work in editing the vBA to suit. The request and append sounds like a good idea to help manage the derived sequential key. If not an alternative may be to leave it blank by default and run a scheduled process on the Master BE to allocate the numbers periodically or when initiated by a user. Thanks also for the Google link. Regards, Penny. > It's funny, when designing databases I always (except for lookup tables) put > a autonumber field as the PK in every table and create the constraints in [quoted text clipped - 102 lines] > > > > Penny > Thanks for your further replies. I am going with adding the > Autonumber to all pertinent tables(which I will ALWAYS do in [quoted text clipped - 4 lines] > process on the Master BE to allocate the numbers periodically or > when initiated by a user. I wouldn't recommend the latter. There's too much possibility for failure involved. SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc > It's funny, when designing databases I always (except for lookup > tables) put a autonumber field as the PK in every table and create > the constraints in code. . . . You mean you don't use indexes in the table definitions to properly restrict entries in candidate keys that could have been the PK? I agree that Autonumbers as surrogate keys are much easier to use (replicated or not), but you still need to enforce the appropriate degree of uniqueness in any candidate keys. Why you'd do that in code, I can't imagine, because that means that the validity of your data is dependent on someone using the application you've designed for it. I think that's bad design -- your basic schema should prevent the addition of incorrect data (insofar as it's possible whatever db engine you're using; i.e., some kinds of business rules would require triggers, which Jet doesn't have), in order that the database can be used with any application written against it without danger of corrupting that data. > . . . That way I'm never tempted to use it as > real data and get into a situation like you are in. But in your > case, for all the work/risks involved, and also especially since > the CandidateID field needs to be sequential, I would be putting > my effort into working out something like "Request and Append" > method. I don't think Penny's situation is unusual in any way. Nor do I think her approach to it mistaken at all. If, for instance, you want invoice numbers to be sequential, you're going to be generating your own invoice number in code, and that's going to present problems for adding invoices in multiple replicas simultaneously that have to be addressed. There is nothing wrong there with having data in your tables that has to be populated in code that is visible to the user. It definitely *is* the case that it's almost never appropriate to use an AutoNumber field for anything other than linking tables, whether or not your back end is replicated. And Penny is migrating from that. That was the point of her first post, asking how to do that. > Taking a step back and looking at it objectively, lets see, your > design is properly SQL normalised and works fine now. You want to > make it replicatable. The problem is you want to keep the > CandidateID sequential in the replicated environment. Right? She already knew the solution to part of the problem, which was switching to a random Autonumber PK in the parent table in order to make relationship enforcement easier. > Option A: Add a new autonumber PK to every table. Every table? Why? Assuming there's only one table that needs a sequential number viewable by the users before replication, surely only that table used a non-Autonumber PK in the first place. The only option is generating the sequence for every table. But Penny did not say she was generating the sequential PK for anything but a single table, tblCandidates. So, I think you presumption that there's any need to add an Autonumber PK to anything but the one table is mistaken. > Work? Lots. Plus you also now have to create the table constraints > in code. . . . Eh? What table constraints are you talking about? Assuming that she was using a generated sequential PK in all her tables (not just as foreign key from a single parent table), an assumption that is completely unwarranted based on Penny's original post, then replacing those keys with an Autonumber adds no new constraints at all, as you're replacing a code-generated key with an engine-created random key. There are no additional restraints on any other fields other than what would have been there in a non-replicated environment. > . . . Risks? High. Too many to list, and when do you update the > front end for the new design? You have to kick the users out until > your finished. . . . Excuse me? Doing this kind of update requires some planning. Any schema-related update requires it. First off, the front end should not be shared, so you won't have to kick anybody out of the front end to make changes to the front end required by changes in the schema. Second, when the schema changes you need a new front end, replicated or not. That means that it's best to roll out a pre-schema-change front end that checks for evidence of the new schema having been implemented, and if it finds it, tells the user that they must upgrade to front end to the new version, and otherwise exits (that's assuming the schema changes are incompatible with the old application version). Or, if you're already using some for of automatic update for the front end (such as Tony Toews auto updater), all you have to do is coordinate the roll-out of the front end with the synch between the production replica and the design master. Third, that would be the case regardless of whether or not the application were replicated. The advantage to the replicated scenario is that you can propagate the schema changes via a synch, instead of having to use code or do them manually. Fourth, rolling out schema changes of any kind, replicated or unreplicated, requires kicking users out of the database. > . . . Fix problem? No. Replica A can still create new > records, incrementing the CandidateID number at the same time as > Replica B. Therefore creating duplicates. See above. These problems are by no means specific to replication -- they are all caused by changes to the schema. > Option B: Leave the fields alone and create a design that allows > only one replica to add records. [quoted text clipped - 4 lines] > users. Fix problem? Yes. The CandidateID is again guaranteed to be > sequential. There is still going to be downtime to the end users if you have any schema changes to existing tables. If you want RI enforced in the new tables that implement your independent solution, that can only be implemented by kicking users out of the data file. > Anyway, it's your choice and I hope it works out well for you. :) I am really confused by the kinds of comments you are making here. They seem to me to be very strange, and completely missing the point of the original problem, despite my having pointed this out to you several times. Are you trying to confuse the matter here or are you trying to help? From where I sit, it doesn't look like the latter. SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc David, "You mean you don't use indexes in the table definitions to properly restrict entries in candidate keys that could have been the PK?" No. I use indexes in the fields that need them. I mean that due to the PK being a single autonumber field, if the true key is multi-column then I enforce the multi-column uniqueness in code. Actually you got me thinking. I just created three tables in a temp database. Two tables with multi-column PK fields and an non-duplicate autonumber field and a third with the autonumber foreign fields from the other two. While it doesn't look the way I expect in the relationships window it does work and saves me from the constraints coding. I shall give that good consideration in future Access databases. Thanks. ""> Option A: Add a new autonumber PK to every table."" "Every table? Why?...But Penny did not say she was generating the sequential PK for anything but a single table, tblCandidates. So, I think you presumption that there's any need to add an Autonumber PK to anything but the one table is mistaken." As per Penny's response "I am going with adding the Autonumber to all pertinent tables (which I will ALWAYS do in future)" > Work? Lots. Plus you also now have to create the table constraints > in code. . . . "Eh? What table constraints are you talking about? " As she said that she will change other tables as well, then if she changes tables with multi-column keys, then yes this will be the case. > . . . Risks? High. Too many to list, and when do you update the > front end for the new design? You have to kick the users out until > your finished. . . . "Excuse me?" Again you answered your own question. There are a lot of steps that need to take place in certain sequences and many places to make a mistake that can create headaches for Penny and the users. "Are you trying to confuse the matter here or are you trying to help? From where I sit, it doesn't look like the latter." Oh well, I guess then that I hope to help Penny and confuse you. :) But at least your thinking and keeping me on my toes. "Cheval" <NoSpam.Ask@ForEmail.com> wrote in news:ovgBe.45023$oJ.33974@news-server.bigpond.net.au: > It's funny, when designing databases I always (except for lookup > tables) put a autonumber field as the PK in every table and create > the constraints in code. . . . You mean you don't use indexes in the table definitions to properly restrict entries in candidate keys that could have been the PK? I agree that Autonumbers as surrogate keys are much easier to use (replicated or not), but you still need to enforce the appropriate degree of uniqueness in any candidate keys. Why you'd do that in code, I can't imagine, because that means that the validity of your data is dependent on someone using the application you've designed for it. I think that's bad design -- your basic schema should prevent the addition of incorrect data (insofar as it's possible whatever db engine you're using; i.e., some kinds of business rules would require triggers, which Jet doesn't have), in order that the database can be used with any application written against it without danger of corrupting that data. > . . . That way I'm never tempted to use it as > real data and get into a situation like you are in. But in your > case, for all the work/risks involved, and also especially since > the CandidateID field needs to be sequential, I would be putting > my effort into working out something like "Request and Append" > method. I don't think Penny's situation is unusual in any way. Nor do I think her approach to it mistaken at all. If, for instance, you want invoice numbers to be sequential, you're going to be generating your own invoice number in code, and that's going to present problems for adding invoices in multiple replicas simultaneously that have to be addressed. There is nothing wrong there with having data in your tables that has to be populated in code that is visible to the user. It definitely *is* the case that it's almost never appropriate to use an AutoNumber field for anything other than linking tables, whether or not your back end is replicated. And Penny is migrating from that. That was the point of her first post, asking how to do that. > Taking a step back and looking at it objectively, lets see, your > design is properly SQL normalised and works fine now. You want to > make it replicatable. The problem is you want to keep the > CandidateID sequential in the replicated environment. Right? She already knew the solution to part of the problem, which was switching to a random Autonumber PK in the parent table in order to make relationship enforcement easier. > Option A: Add a new autonumber PK to every table. Every table? Why? Assuming there's only one table that needs a sequential number viewable by the users before replication, surely only that table used a non-Autonumber PK in the first place. The only option is generating the sequence for every table. But Penny did not say she was generating the sequential PK for anything but a single table, tblCandidates. So, I think you presumption that there's any need to add an Autonumber PK to anything but the one table is mistaken. > Work? Lots. Plus you also now have to create the table constraints > in code. . . . Eh? What table constraints are you talking about? Assuming that she was using a generated sequential PK in all her tables (not just as foreign key from a single parent table), an assumption that is completely unwarranted based on Penny's original post, then replacing those keys with an Autonumber adds no new constraints at all, as you're replacing a code-generated key with an engine-created random key. There are no additional restraints on any other fields other than what would have been there in a non-replicated environment. > . . . Risks? High. Too many to list, and when do you update the > front end for the new design? You have to kick the users out until > your finished. . . . Excuse me? Doing this kind of update requires some planning. Any schema-related update requires it. First off, the front end should not be shared, so you won't have to kick anybody out of the front end to make changes to the front end required by changes in the schema. Second, when the schema changes you need a new front end, replicated or not. That means that it's best to roll out a pre-schema-change front end that checks for evidence of the new schema having been implemented, and if it finds it, tells the user that they must upgrade to front end to the new version, and otherwise exits (that's assuming the schema changes are incompatible with the old application version). Or, if you're already using some for of automatic update for the front end (such as Tony Toews auto updater), all you have to do is coordinate the roll-out of the front end with the synch between the production replica and the design master. Third, that would be the case regardless of whether or not the application were replicated. The advantage to the replicated scenario is that you can propagate the schema changes via a synch, instead of having to use code or do them manually. Fourth, rolling out schema changes of any kind, replicated or unreplicated, requires kicking users out of the database. > . . . Fix problem? No. Replica A can still create new > records, incrementing the CandidateID number at the same time as > Replica B. Therefore creating duplicates. See above. These problems are by no means specific to replication -- they are all caused by changes to the schema. > Option B: Leave the fields alone and create a design that allows > only one replica to add records. [quoted text clipped - 4 lines] > users. Fix problem? Yes. The CandidateID is again guaranteed to be > sequential. There is still going to be downtime to the end users if you have any schema changes to existing tables. If you want RI enforced in the new tables that implement your independent solution, that can only be implemented by kicking users out of the data file. > Anyway, it's your choice and I hope it works out well for you. :) I am really confused by the kinds of comments you are making here. They seem to me to be very strange, and completely missing the point of the original problem, despite my having pointed this out to you several times. Are you trying to confuse the matter here or are you trying to help? From where I sit, it doesn't look like the latter. SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc > "You mean you don't use indexes in the table definitions to > properly restrict entries in candidate keys that could have been > the PK?" No. I use indexes in the fields that need them. I mean > that due to the PK being a single autonumber field, if the true > key is multi-column then I enforce the multi-column uniqueness in > code. Why? To me, that's risking your database because it depends on the application to maintain data integrity. Data integrity should be maintained by the database engine, as part of the schema definition (including indexes). > Actually you got me thinking. I just created three tables in a > temp database. Two tables with multi-column PK fields and an [quoted text clipped - 3 lines] > the constraints coding. I shall give that good consideration in > future Access databases. Thanks. It would be what I consider to be standard practice. Now, if you don't have a real candidate key that can be enforced by an index, then, lacking triggers, you will need to use code to enforce your definition of uniqueness. This is a very common scenario in tables storing data on people, since applications often have the requirement to be able to create a record for a person without having the complete data that would be needed to insure uniqueness in an index (i.e., fields in the candidate key need to allow nulls, which means no unique index). > ""> Option A: Add a new autonumber PK to every table."" > "Every table? Why?...But Penny did not say she was generating the [quoted text clipped - 3 lines] > As per Penny's response "I am going with adding the Autonumber to > all pertinent tables (which I will ALWAYS do in future)" Well, I was surprised at that one. I don't know why anyone would use a generated sequence for fields that aren't exposed to users, and in most data hierarchies, the PK of the child tables just doesn't matter. So, for replication, that means the random autonumber doesn't cause a problem, and it also means that before replication, there's no reason to program your sequence. So, that's what confused me -- I couldn't imagine a scenario where all tables other than the one she mentioned would have a generated sequence. Yes, it's not uncommon to have a couple or three such tables in an app, but certainly not *all* the tables. To me, then, it looks like her original schema design was quite unusual. >> Work? Lots. Plus you also now have to create the table >> constraints in code. . . . [quoted text clipped - 3 lines] > changes tables with multi-column keys, then yes this will be the > case. But not in *code*. Yes, she may end up with a useless generated sequence if she doesn't change the code, but the "table constraints" should not have been enforced in code in the first place, but in the table and RI definitions. >> . . . Risks? High. Too many to list, and when do you update the >> front end for the new design? You have to kick the users out [quoted text clipped - 4 lines] > that need to take place in certain sequences and many places to > make a mistake that can create headaches for Penny and the users. But none of those steps are unique to a replicated back end -- all of them apply in one degree or another to every database application in which you are applying a schema update. > "Are you trying to confuse the matter here or are you trying to > help? From where I sit, it doesn't look like the latter." > > Oh well, I guess then that I hope to help Penny and confuse you. > :) But at least your thinking and keeping me on my toes. Well, I'm glad you answer questions in the group. Since MichKa has moved on to internationalization, somebody has to keep the flame alive. SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc MichKa, yeah? I was wondering why I haven't seen his postings for a while... "Cheval" <NoSpam.Ask@ForEmail.com> wrote in news:7_XBe.49658$oJ.14659@news-server.bigpond.net.au: > "You mean you don't use indexes in the table definitions to > properly restrict entries in candidate keys that could have been > the PK?" No. I use indexes in the fields that need them. I mean > that due to the PK being a single autonumber field, if the true > key is multi-column then I enforce the multi-column uniqueness in > code. Why? To me, that's risking your database because it depends on the application to maintain data integrity. Data integrity should be maintained by the database engine, as part of the schema definition (including indexes). > Actually you got me thinking. I just created three tables in a > temp database. Two tables with multi-column PK fields and an [quoted text clipped - 3 lines] > the constraints coding. I shall give that good consideration in > future Access databases. Thanks. It would be what I consider to be standard practice. Now, if you don't have a real candidate key that can be enforced by an index, then, lacking triggers, you will need to use code to enforce your definition of uniqueness. This is a very common scenario in tables storing data on people, since applications often have the requirement to be able to create a record for a person without having the complete data that would be needed to insure uniqueness in an index (i.e., fields in the candidate key need to allow nulls, which means no unique index). > ""> Option A: Add a new autonumber PK to every table."" > "Every table? Why?...But Penny did not say she was generating the [quoted text clipped - 3 lines] > As per Penny's response "I am going with adding the Autonumber to > all pertinent tables (which I will ALWAYS do in future)" Well, I was surprised at that one. I don't know why anyone would use a generated sequence for fields that aren't exposed to users, and in most data hierarchies, the PK of the child tables just doesn't matter. So, for replication, that means the random autonumber doesn't cause a problem, and it also means that before replication, there's no reason to program your sequence. So, that's what confused me -- I couldn't imagine a scenario where all tables other than the one she mentioned would have a generated sequence. Yes, it's not uncommon to have a couple or three such tables in an app, but certainly not *all* the tables. To me, then, it looks like her original schema design was quite unusual. >> Work? Lots. Plus you also now have to create the table >> constraints in code. . . . [quoted text clipped - 3 lines] > changes tables with multi-column keys, then yes this will be the > case. But not in *code*. Yes, she may end up with a useless generated sequence if she doesn't change the code, but the "table constraints" should not have been enforced in code in the first place, but in the table and RI definitions. >> . . . Risks? High. Too many to list, and when do you update the >> front end for the new design? You have to kick the users out [quoted text clipped - 4 lines] > that need to take place in certain sequences and many places to > make a mistake that can create headaches for Penny and the users. But none of those steps are unique to a replicated back end -- all of them apply in one degree or another to every database application in which you are applying a schema update. > "Are you trying to confuse the matter here or are you trying to > help? From where I sit, it doesn't look like the latter." > > Oh well, I guess then that I hope to help Penny and confuse you. > :) But at least your thinking and keeping me on my toes. Well, I'm glad you answer questions in the group. Since MichKa has moved on to internationalization, somebody has to keep the flame alive. SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.