L
LurfysMa
I would like to hear opinions on the tradeoffs of putting the tables,
forms, and queries for several related datasets in separate databases
vs one combined database.
I am working on an application that will have a number of "subjects".
The subjects have a number of commonalities, but are not identical. I
am hoping that I will discover that the subjects fall into a few
"types" and a common database structure can be used for each type.
Each subject will have a few (1-5?) tables, queries, forms, etc. They
have a lot of similarities, but are not identical. Some subjects will
have more tables than others. Even the tables that are the same may
have some different fields.
Initially, I had all of the subjects in one database. I actually tried
to use the same tables for all of the subjects. This quickly became
unweildy.
Now I want to make separate tables, forms, & queries for each subject.
If I find that there are commonalities, I will combine them later.
The questions is whether to put each subject in a different database
or just make separate tables, forms, & queries in a combined database.
I am inclined to make separate databases. My reasons are as follows. I
would appreciate comments on whether my reasons are valid or if there
are other considerations.
The advantages of separate databases as I see it are:
1. Smaller database files, easier to transport?
2. Slightly easier to replace an entire db than a table within a db.
3. Simpler naming. No need to identify the subject.
4. Better for incremental backup. Changes to one subject would not
affect a huge database file.
The disadvantages are:
1. More files to manage.
2. Where to put the master subject table?
3. Somewhat more complex for the application.
I am not sure if there is any difference performance wise.
Comments?
forms, and queries for several related datasets in separate databases
vs one combined database.
I am working on an application that will have a number of "subjects".
The subjects have a number of commonalities, but are not identical. I
am hoping that I will discover that the subjects fall into a few
"types" and a common database structure can be used for each type.
Each subject will have a few (1-5?) tables, queries, forms, etc. They
have a lot of similarities, but are not identical. Some subjects will
have more tables than others. Even the tables that are the same may
have some different fields.
Initially, I had all of the subjects in one database. I actually tried
to use the same tables for all of the subjects. This quickly became
unweildy.
Now I want to make separate tables, forms, & queries for each subject.
If I find that there are commonalities, I will combine them later.
The questions is whether to put each subject in a different database
or just make separate tables, forms, & queries in a combined database.
I am inclined to make separate databases. My reasons are as follows. I
would appreciate comments on whether my reasons are valid or if there
are other considerations.
The advantages of separate databases as I see it are:
1. Smaller database files, easier to transport?
2. Slightly easier to replace an entire db than a table within a db.
3. Simpler naming. No need to identify the subject.
4. Better for incremental backup. Changes to one subject would not
affect a huge database file.
The disadvantages are:
1. More files to manage.
2. Where to put the master subject table?
3. Somewhat more complex for the application.
I am not sure if there is any difference performance wise.
Comments?