Splitting one back-end database into THREE...

[bclm]

I split a single back-end database with lots of one-to-many relationships into three separate databases and maintained the appropriate relationships in each new and separate database. I have a client front-end that is installed on 10 computers and "LINKS" all the relationships from the now separate databases into the application. From the application, it looks the same and maintains the relationships with the exception of referential integrity. I have two questions regarding this scenario...

1) I have had several crashes and had to restore from backup; something that never happened when it was all in one backend database. Did I overlook something? I thought (which may be dangerous) that linking was the way to bring separate databases into one place and keep relationships. Maybe it's record locking... I don't know. One user got the old "Unrecognized Database... do you wish to repair?" message, at which point the repair was futile and I had to restore from backup..

2) What about that referential integrity?? I have error trapping in code to keep orphan records away, but it was nice to have that feature as a backup. It sees the one-to-many relationships. Will it maintain the relationships and give warnings or is this where corruption can occur???

 

[JeremyNYC]

Start from the basics. _WHY_ did you split it into three back ends?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[bclm]

I split it for several reasons. First, for size. There is a one gig limit (access 97) so I've been told. To keep all in one would reach the limit sooner. There will be three separate dabases that interact with each other with three separate applications, but all use the same tables interchangeably. Does this help?

 

[JeremyNYC]

Are you anywhere near the one gig limit? Do you realistically think you will be? I've never actually seen it done in an applicatin that made sense to be used with an Access database.

The fact that there will be three databases that interact with each other and use the tables interchangably is a very strong argument for having only one back end.

In general it's best to have only one back end, whenever possible.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[bclm]

I split it for several reasons. After it proves stable, I will be building another front end app. SO, I will have two separate apps that use some of the tables from each of the databases. Because both apps need to share certain data, I thought splitting the data up was the best place to start. Also, I read that for size limits, you can break up a large database into smaller ones to overcome the one gig limit (access 97). To keep all in one would reach the limit sooner. Does this help?

 

[markphsd]

What size are you at now? Jeremy is right, 1 gig would be really large for an access db. And, considering the amount of time you are dedicating to this, is moving to access 2000 an option, it has a larger limit doesn't it? And it should perform better anyways. You might want to consider archiving data before you consider splitting the db into three sections. Why three? Why not 2? Why not 4? Where's the connection between how many sections you split it into. I think the major question is, what is the current back end size. I write code in cuneiform, what about you?

 

[annsolomon]

I like markphsd's question about why 3. Why not 2? Why not 4? You really didn't explain but it may not be important to the issue.

The point I really want to make is that I sometimes "split a backend" into 2 databases so I can imagine there could be a reason to split further. Our research department does CATI surveys that brings in data which we use for various clients. I decided to keep the "common" data in one back end and the "unique" data in another back end. This way when we prepare reports for a client we access some data from their "unique" back end and other data from the "common" back end. To put it simply, it saves server space.

Why did you set up relationships between your 3 backends? Or, did I misunderstand you? The relationships are better established between the tables in your front end. Ann

 

[bclm]

Thanks for responding. I ended up putting everything back in one back end, since my users experienced crashes and I even had to restore from backup once. YUCK! The size is not really an issue today... but future yes. My understanding is that you could have many one-gig databases working with each other to overcome the size limits. I cannot move into access 2000, because I am in a large health care system and the standard is still office 97.... YUCK again! Sooo, here's my scenario... I have an application that needs to be split into three separate working apps... that was the need for three databases. Since it is a patient-based application, one patient can be in all three components. So, my theory was to break the database into clients, services, providers..... and link the appropriate tables into each separate app. DOes this make more sense?

 

[annsolomon]

As I mentioned in my last post, you should have no problem linking a frontend database application to tables in more than one backend database. Ann

 

[markphsd]

bclm,
Hey neat, I work in HealthCare to. But we are up to, oh say.. 40 megabytes of info in the back end. And we have about 40,000 patients in a patient table and many related tables with more information. Its take years to get that large. Mark P.

I write code in cuneiform, what about you?