Best practice for multiple backend DBs; Linked tables? 1

[nunzio4]

Summary:
When is it appropriate to create two related backend databases with linked tables vs. putting them all together in one database?

Detail:
I am creating an application that has two main subject areas: Contracts and Transactions. Each Transaction must be related to a Contract, but aside from this relationship the two subjects differ dramatically, both in design and use. Transactions will be hit frequently, but Contracts exists only as a reference, and will only be hit when assigning a foreign key to a Transaction.

It seems that my application should run faster with separate backends, but is there an argument to be made for joining them into one .mdb?

Thank you!

 

[mikey69]

Multiple back-ends are a pain to maintain and will probably slow things down when looking at data in both. Based on what you say, I'd put all the data in one back end and pay attention to relationships and indexing.

Access makes all things possible. It even makes them intelligible

 

[wemeier]

I agree with mikey69, but for different reasons. When multiple linked tables reside in different physical databases, all of the databases must be opened each time the application is opened. This can increase the memory used by the application and can slow down loading the application.

Also, since the two tables will share a foreign key, putting them in separate databases means that both databases must be kept in sync. If either database is restored or moved, you've lost your integrity.



Cruising the Information Superhighway
_{(your mileage may vary)}