suggestions please - create a really big DB or maintain smallr DBs?

[mdbBound]

Hello All,

I have this database from Paradox that I want to convert to Access and I need suggetions whether to maintain the 8 small databases or to put them together as one big database.

Currently (Paradox), we have:

1. JV Database (process JV - input from users)
2. SystemData Database (summarize data from mainframe)
3. Conversion Database (convert accounts)
4. Trial Balance Database (prepare a trial balance)
5. Financial Statements (FS) Database (prepare the final Financial Statements with transfer to Excel)
6. Auditors Database (to prepare commonly requested schedules by the auditors).

On each database we have approx. 15 tables, 40 queries, 10 reports and 7 scripts to run the sequence of queries.

If I keep the current structure, I will have to jump from one database to another to update them (when ever there is a change in the final financial statements. However, this keeps each process clean and faster since it is a small database.

If I put them into one big database, I am thinking of having some buttons to PREPARE TRIAL BALANCE, PREPARE FIN STATEMENT, GET SYSTEM DATA and so on.... and avoid jumping from one Db to another.

Please advise which is better for my situation.

Thank you.

 

[spacepro]

Would it be possible to have several databases, but have one main database with all of the tables linked?

Just a thought.

Andy

 

[mdbBound]

Earlier, I thought of linked tables too but why maintain seperate dbs for tables only and have all the queries ( the bulk of processing in) one database. Will it not be slower since the process will need to get data from tables outside the main database.

This is the system administration portion of the application. Only the administrator will use this. It will reside in my pc.

There is another database (in network) that multi-users will interact with. I will link tables with this to get the new or updates from users.

Thank you for your insights.

 

[Len Boorman]

Why not look at the data, In the eight databases at present is data duplicated at all across databases. ?.

Think I would start there and then depending on what I found develop a schema.

It is possible that the data lends itself to say two distinct areas so you have two databases. Maybe then you have three interfaces. One for each database for people who only need to access on set of data and then a third for those that need both data sets.

Question is not 8 or 1.

Question is How should I handle these data sources ?.

Just thinking

Len B