April 29, 2013 at 3:35 pm
I'm planning on moving around 4 or 5 MS Access databases to SQL Server and need some insight on what might be the best approach in regards to the following:
First what the issue is: I have a client database which has pretty generic information about some people (Jobs, referral info, etc..) I will refer to this as my master database. These other databases (let's call them program databases) all have similar information, but are not connected to the master. :angry:
I'm trying to figure out if moving them to 1 database is the answer, or to each their own database on SQL.
The master will have all things client/job/referral related to it and will definitely need to be available for read/write for all the program databases.
Each time a client enrolls into a program, they will have to be in the master first and then they would be available for use for the programs.
These program databases are for grant based programs and can easily come and go, so there is a good chance that at some time, the tables or databases will no longer be used once the grant expires or some other reason. One of the other gotcha's is that I'm a creature of habit and each one of the program databases has a couple tables that are named the same.
That being said, I just need a little direction from anyone that may have some pros and cons on separating these out by databases or just by tables. If more specifics are needed let me know, I didn't want to get too detailed and make my question convoluted.
Thanks,
Jeff
April 29, 2013 at 9:41 pm
jbloes (4/29/2013)
I'm planning on moving around 4 or 5 MS Access databases to SQL Server and need some insight on what might be the best approach in regards to the following:First what the issue is: I have a client database which has pretty generic information about some people (Jobs, referral info, etc..) I will refer to this as my master database. These other databases (let's call them program databases) all have similar information, but are not connected to the master. :angry:
I'm trying to figure out if moving them to 1 database is the answer, or to each their own database on SQL.
The master will have all things client/job/referral related to it and will definitely need to be available for read/write for all the program databases.
Each time a client enrolls into a program, they will have to be in the master first and then they would be available for use for the programs.
These program databases are for grant based programs and can easily come and go, so there is a good chance that at some time, the tables or databases will no longer be used once the grant expires or some other reason. One of the other gotcha's is that I'm a creature of habit and each one of the program databases has a couple tables that are named the same.
That being said, I just need a little direction from anyone that may have some pros and cons on separating these out by databases or just by tables. If more specifics are needed let me know, I didn't want to get too detailed and make my question convoluted.
Thanks,
Jeff
It definitely sounds like you are leaning towards creating a database for each program, which might be the best way to go. It would probably depend on how this data is being accessed. It is being accessed only through SSMS or through different applications? For ease of use, it might be best to leave everything in one database and instead of breaking the data out into different tables, put the data into one table and put an identifier (PK or FK) that will point to what kind of program it is. Is there a reason for keeping the data separate besides them being different grant programs? If not, I would say put them all in the same table with an identifier. That way, if there are applications, you don't need to point these at different databases or tables, just one table.
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 29, 2013 at 9:53 pm
Well, the more I've been thinking about it, the more I'm leaning to having one database with everything under it.
I may lean towards utilizing a schema per program since each programs essential data can vary greatly from each other. I would be left with something like:
ProgramA.tblProgramData
ProgramB.tblProgramData
...and so on.
Then each tblProgramData could reference the dbo.ClientData table for the foreign key field with ease. I like how I can then leverage the schema for security and permissions.
I guess after a period of time, I could be left with a bunch of tables that are no longer used once the program is no more. That part I'm not crazy about.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply