New Database or just add more tables to an existing database (1 Viewer)

[Umpire]

This is a discussion in theory / best practice only.
How do you decide when to create a second, or third etc. database instead of just adding more tables to your existing database?
What so called "tips the scales" one way or the other?

I am still very new to this so kind of looking for a mindset to have before I need to make this decision.

Thanks

 

[Pat Hartman]

1. If the data in the new tables will be related to data in the existing tables, add the new tables to the old BE.

2. If the same users will be using both applications, add the new functionality to the old FE.

Those are just general guidelines. At some point either the FE or BE might become too unwieldy so keep that in mind also. However #1 is very important since you can only enforce relationships WITHIN a single BE database. You can join data from multiple databases but that is not the same thing as enforcing Referential Integrity which is what relationships do.

 

[plog]

First, I agree with Pat's answers to the questions you wrote. However, reading between the lines of your questions, I heard some troubling things that make me think you haven't normalized your database properly. It sounds as if you are adding tables to accomodate new data that should be easily accomodated in a properly structured database.

Why are you constantly adding new tables? Everytime you get a new supplier you add a table with that Suppliers name to accomodate the products they have? Or every month or year you are adding a new table to accomodate that month's or years data? Am I close? Why do you foresee adding a lot of new tables? What data will they hold?

 

[Umpire]

First, I agree with Pat's answers to the questions you wrote. However, reading between the lines of your questions, I heard some troubling things that make me think you haven't normalized your database properly. It sounds as if you are adding tables to accomodate new data that should be easily accomodated in a properly structured database.

Why are you constantly adding new tables? Everytime you get a new supplier you add a table with that Suppliers name to accomodate the products they have? Or every month or year you are adding a new table to accomodate that month's or years data? Am I close? Why do you foresee adding a lot of new tables? What data will they hold?

Fair Questions.
Currently I have a database for Inspection / Repair of items that come through my area. 90% of the items come in with a specific type of paperwork. (Source A is 90% source B & C 10%) The database is set up around that paperwork IE; the information it contains etc. for the other 10% we still record the test/repair data, but we do not record a count of the other types of paperwork specifically. I have been hearing from management that they are considering having us record the details on source B and C paperwork in addition to our source A paperwork.

The Source B paperwork additional information could probably be handled by adding a few fields or possibly a separate table just for the "extra" information.

However I have seen many discussions on here about multiple databases and was wondering if that might be a better approach. I don't think so but you don't know if you don't ask.

I figured if I had a better understanding of when to use a separate database and when not to, then next year (when they are looking to have me implement this change) I will be better prepared to make the correct choice.

 

[Pat Hartman]

If the data in db1 has no relationship/depencency on the data in db2, then they belong in separate databases. Sometimes you will find that you have core tables that are used in multiple applications. To enforce RI, you need to put the tables in the same database. However, when you are making schema diagrams, you will find that you have several star clusters hanging off a central table. Each of the clusters is supporting a different aspect of the business. You will eventually need to move this data to SQL Server to prevent it from being unwieldy so plan ahead.