Relating tables in different databases

I have several Access databases on my web server that are queried with Coldfusion. I am currently designing a new database to centralize some data. I am planning to link to tables in the new database from the other databases and I am wondering what I should use for primary keys in tables in the new database (which will be foreign keys in tables in the other databases).

Since there is no natural primary key, I would normally just create an autonumber primary key column, but I feel a little uneasy about doing that in this situation. It's probably irrational, but it just makes me nervous to have such an abstract value providing the only link between tables in several different databases.

I'm just looking for suggestions from people who have successfully handled such sitations, specifically what you recommend for the primary key and anything else you have to add.

My advice to you is to take a step back and just build a single database (using a real database, not Access) for all the applications instead of attempting to link various databases. You can often get away with that with real databases (with a non-trivial performance hit), but the chance of corruption using Access starts to reach 100% very quickly. It will take some extra time to redevelop and redeploy the applications, but instead of forcing a dozen eggs to act like a chicken, you wind up with a fully functional chicken (bad example, perhaps). If what you are doing is important, you should not be relying on a bunch of diverse Access databases in the first place.

I don't like Access much either, and I didn't choose it. But the reality is, I have some applications running on Access / Coldfusion and that's not going to change any time soon.

I don't consider lumping several logical databases into one physical database a good design choice.

Please explain how the likelihood of corruption will increase as a result of linking tables in different databases.

Since Access is really a pretty front end to a file manager (as opposed to a single process owning the data and interacting with others via some sort of IPC), it has to play lots of games to ensure that multiple processes don't modify data at the same time. MySQL plays the same tricks (meaning there is no single memory resident instance that is exclusively in charge of the data). As the number of simultaneous writes (reads are better tolerated, though performance often goes down as they increase) increases, the overhead to manage the exclusive access increases. As long as the meta-process in charge remains so, it can channel the writes one at a time. However, any process can initiate a write (even a read, under certain circumstances), but not complete it, and lock up access for all the other processes. As you link different processes into multiple databases the ways they can interact increase geometrically. You want a master database to be able to consolidate the data from the 'children' database (for reporting, I presume) and as long as that is read-only you may be able to get a way with it for a while. However, as the complexity goes up the chance to accidentally create some sort of interdependency and wind up with problems grows quickly.

You could, as I suggested, put them all into a single database (it makes queries much easier) and use naming conventions to 'control' access to the various 'databases'. I used this extensively (in SQLServer) for several years to great effect (security was handled at the application level). Since you are using Access, there is currently nothing keeping any other process from interacting with the data anyway, so your security is not impacted. You could even do this consolidation in Access, though the competition for resources may outweigh the benefits.

If you were to have all the databases in a single instance of, say SQLServer, then you can access them individually or as a group (multiple instances have more overhead, and if they are on separate servers the overhead is higher still). You can control access to individual databases with fine granularity (it is not possible, I believe, to control rights with Access at all) and give each process rights to only its database. The single instance (of SQLServer in this example) can control the read/write access to the various databases to ensure that the data remain consistent and provides a lot of help in automagically resolving locking and competition for resources (it is not perfect, but I am willing to place a lot of money that it is orders of magnitude better than Access).

Just out of curiosity, what kind of web site are you operating that uses Access as a production database?

The situation is that I have several databases supporting different applications. I have noticed that each database is duplicating certain data and in order to eliminate the redundancy, and overhead of maintaining multiple instances of the same data, I am going to store that data in a new database, which the existing ones will then reference.

These databases handle many more reads than writes. I routinely compact and repair them.

When you say interdependency, do you mean in terms of processes? Obviously the data will be interdependent (related).

The queries would be just as easy with the linked tables. In fact, in that sense it would be easier because I wouldn't have to prefix all of my table names. And if I need to download one of the databases and open it in the Access client, I wouldn't have to download one massive file every time.

Unfortunately I do not have use of SQL Server, I'm stuck with Access for now.

These databases store content that is retrieved and displayed with Coldfusion for use on several dozen sites.

I'm not sure what you mean by 'what type of site' -- I have a number of sites running various Access / ColdFusion applications.

Thanks for your suggestions.

There are free enterprise level databases out there, and they can even be made to run on Windows (Postgresql is what I like).

The purpose of my post is not so much to rant about Access (though I am sure you detected a bit of that), but to get you to consider a redesign. I know the old story about how all it needs is just this little tweak, but at some point you are going to spend hours (or much more) on problems that start to crop up. If all you are using the database for is a static reference to data, why not just put the data in flat files or even prebuild HTML? Every time you go to access the database there is a lot of overhead associated with the calls, simply reading in data from a file is likely to be much less expensive. I understand the ease of use in putting data in a database instead of a file and have done many of the same sorts of things (I even stored client state for a VB program in a database because I was too lazy to create ini files), but I firmly believe you are asking for heartache using all these Access databases.

The interdependancies I am talking about is the competition between processes because of the interdependancies of the data. A 'simple' change to a table might cause a cascade of events that are difficult to predict and reproduce.

I understand what you're saying, and to an extent I agree with you.

When I first entered my current position, I only had the Access / ColdFusion environment to work with. I've been responsible for moving all new development onto the LAMP platform. However, much of what began in the Access / ColdFusion platform is stuck there for now. It wasn't my decision to use that platform, and I can't decide to stop using it now.

If I am going to run into problems because of limitations of Access, that's one thing, but my design is solid. If I were developing this in MySQL, I would just create a new database, create my new tables in that, and reference them in any query using the database.table convention.

The data is not static, but as I said, the databases are read from far more often than they are written to. I think I have a pretty solid grasp of what relational databases are useful for, and this is one of those situations.

About that I am willing to be concerned. I don't really know the details of how Access handles locking. If you know of a specific scenario in which I will encounter a problem due to linking to a table in a different database, I would very much like to hear about it. So far what you have told me is too vague for me to act on in any way.

I stopped using Access with version '97. As the number of users increased beyond 5 or so (reading or writing), we started to routinely get corrupted databases. This was with single databases, not trying to cross reference them. It is possible that the upgrade to Access 2000 came with more than just a name change and this issue has gone away, but I would't put much money on it. I know you gotta do what you gotta do, I became an expert on VB and Perl because my employers at the time insisted that I use them instead of something better (but I was able to get them weened off Access).

Good Luck! I hope you don't need it.

Yeah, thank you.

If anybody releases a database server that corrupts databases due to just a few reads going on at once, that is just f*****.

I just don't really see how linking to the table in another database is going to be that much more of a problem than linking to a table in the same database. Of course, there's no way you could know my particular setup as well as I do. I'd be more worried about creating one massive Access database than doing it this way.

Actually, the problem I am having at the moment is that Access does not want to allow me to do it (see other thread).

Thanks