Design question

[rs2k]

Hi,

I started to build a database for my wife's company with the intention of 'One database to do everything!' I quickly realised what a mammoth task this involved and started losing myself in queries & code.


I have now split it into smaller chunks;

• Employees db - Employee info and timekeeping.
• Client db - Client info.

I am currently building a 'Bookings/Invoicing/Payment' db and have linked tables back to the Client db.

The nature of the business lends itself to doing small projects (Weekend breaks away spread over a few months etc...) which I'm hoping to manage with a db, again linked back to the clients db and the Bookings/Invoicing/Payment db.

So my question is; Is this good practice to link into other db's, or will something come back and bite me?

Many thanks
Colin

 

[MarkK]

If the data is related I'd keep it all in one file as much as possible. Like if the clients are the same ones doing the bookings and the same ones you invoice, and so on, it makes sense if they are in the same file. The data belongs together, so it doesn't make sense if it's not available to consumers at the same time.

That said, they don't have to be. It's not a matter of performance. A thing to explore is can you enforce referential integrity between linked tables not in the same file, and if you can, there's no real performance reason to use the same file.

The most common architecture though, will be a front-end/back-end split, so I'd put all the tables in one file on a server or a single computer on a network, and then put all the forms and queries and reports and code in a different file. This way you decouple the data from the user interface and business logic, and you can very easily update, upgrade, modify, and tweak the front end file without risking harming the data. Then you deploy your latest program file to the "live" network environment.

hth

 

[rs2k]

Hi lagbolt,

Thanks for the reply. Sorry, I should have mentioned that they are already split and on a server, my apologies.

My initial thought is that they would be easier to maintain if they were relatively small and I could just link to them as and when required. But, the more I think about it, the more I realise that my table structure should accommodate any new projects.

I will now combine the Clients db & Bookings/Invoice/Payments db into one and have a re-think with the structure.

Thanks again, sometimes you can't see the wood for the trees!

Cheers
Colin