Design for future merging
in [Tables]
|
Prev: Creating links
Next: Automatic Annual Leave
From: Rlong via AccessMonster.com on 22 Apr 2010 18:16 I've created a small but somewhat complex relational database that uses autonumber fields in higher level tables as primary keys to link with foreign keys in lower level tables. At this point I have up to 5 levels of tables. I'd like to copy this database for use at 4 other remote sites, with the ultimate intention of merging all 5 back together after a few months of data entry. I've read quite a bit about how to merge databases that weren't originally designed with future merges in mind, and this ends up being quite complex with so many levels and autonumber-dependent tables. I'm wondering if there is a way that I can create the duplicate databases from the outset that would make future merging easier? For instance, by causing the autonumbers at each different site to either start at a particular point (e.g., one site be the 100000s and another the 200000s), or, by using the "random" setting for autonumber such that no two autonumbers in the same table were identical (although my sense is that "random" introduces its own problems). Any thoughts on how to make the future merge easier would be helpful. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201004/1
From: Jeff Boyce on 22 Apr 2010 19:30 I didn't notice which version of Access you are using. This is important because the Replication feature went away for the ?2007 version. If you're up to it, that might be a way if you're using the earlier (pre- 2007) versions. An alternate approach might be to add in a single field that holds the location. The combination of your autonumber PLUS the location/facility/site/... will give you a way to identify which records belong to which site. Now, if you were trying to use Access Autonumbers as sequence numbers, you're in for a bit of pain. Access Autonumbers are intended to uniquely identify rows. That's it! Not guaranteed sequential, can/will have missing values. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Rlong via AccessMonster.com" <u58125(a)uwe> wrote in message news:a6ee3a8f8ad28(a)uwe... > I've created a small but somewhat complex relational database that uses > autonumber fields in higher level tables as primary keys to link with > foreign > keys in lower level tables. At this point I have up to 5 levels of tables. > I'd like to copy this database for use at 4 other remote sites, with the > ultimate intention of merging all 5 back together after a few months of > data > entry. I've read quite a bit about how to merge databases that weren't > originally designed with future merges in mind, and this ends up being > quite > complex with so many levels and autonumber-dependent tables. I'm wondering > if > there is a way that I can create the duplicate databases from the outset > that > would make future merging easier? > > For instance, by causing the autonumbers at each different site to either > start at a particular point (e.g., one site be the 100000s and another the > 200000s), or, by using the "random" setting for autonumber such that no > two > autonumbers in the same table were identical (although my sense is that > "random" introduces its own problems). > > Any thoughts on how to make the future merge easier would be helpful. > > Thanks > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201004/1 >
From: Rlong via AccessMonster.com on 22 Apr 2010 20:37 Thanks, Jeff. Yes, I am using 2007. I'm not sure what the "replication" feature was, but it sounds like it isn't an option anyway for 2007. So, regarding the combination field--I'm unclear how this would work, as I didn't think you could create a "calculated field" in a table. And, assuming I did do this, would I need to do this for all the primary key/foreign key combinations manually throughout the database? As you can tell, I'm on the newer end of the continuum when it comes to database construction, and have little coding experience--most of what I've done is menu-driven. Thanks Jeff Boyce wrote: >I didn't notice which version of Access you are using. This is important >because the Replication feature went away for the ?2007 version. If you're >up to it, that might be a way if you're using the earlier (pre- 2007) >versions. > >An alternate approach might be to add in a single field that holds the >location. The combination of your autonumber PLUS the >location/facility/site/... will give you a way to identify which records >belong to which site. > >Now, if you were trying to use Access Autonumbers as sequence numbers, >you're in for a bit of pain. Access Autonumbers are intended to uniquely >identify rows. That's it! Not guaranteed sequential, can/will have missing >values. > >Good luck! > >Regards > >Jeff Boyce >Microsoft Access MVP > >> I've created a small but somewhat complex relational database that uses >> autonumber fields in higher level tables as primary keys to link with >[quoted text clipped - 22 lines] >> >> Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201004/1
From: Fred on 23 Apr 2010 08:48 I think that you will need to be thinking about your "data" mission and data specifics with all of these databases as a being the core of answering this question. (or maybe you have already and didn't describe it to us.) For example: Are these different locations editing records that are for the same entity (e.g. updating data for a particular person at multiple locations) Are these different locations just adding records, or are they editing existing records? If it's the latter, what will they be looking at to edit? Etc. Once you get those kinds of things decided, then those decision should how you created your structure. Replication was, in essence, to manage having, (and, more to the point, editing on) multiple copies of the same database.
From: Rlong via AccessMonster.com on 23 Apr 2010 13:01
Thanks for the continued input. The different locations are simply entering new data, and won't need to see or edit data from the other locations or the base location. After 3 months of data entry, each offsite database will ideally be brought into the base database and use at other locations will be discontinued. Thanks. Fred wrote: >I think that you will need to be thinking about your "data" mission and data >specifics with all of these databases as a being the core of answering this >question. (or maybe you have already and didn't describe it to us.) > >For example: > >Are these different locations editing records that are for the same entity >(e.g. updating data for a particular person at multiple locations) > >Are these different locations just adding records, or are they editing >existing records? If it's the latter, what will they be looking at to edit? > > >Etc. > >Once you get those kinds of things decided, then those decision should how >you created your structure. > >Replication was, in essence, to manage having, (and, more to the point, >editing on) multiple copies of the same database. -- Message posted via http://www.accessmonster.com |