July 6, 2018 at 11:50 am
As a way to keep my 72y/o brain stimulated, I am working on developing a C#/SQL server 2017 based health care system. My question is would I be better off creating separate SQL dbases for each of ten subsystems(member, provider, insurance, etc.
Thanks!
July 6, 2018 at 12:15 pm
With that little information, it's very hard to give a useful answer.
The things that will determine the answer are whether you need referential integrity between the systems, whether you want to backup/restore them as a unit and whether you'll ever need to restore one of the subsystems to an earlier point in time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2018 at 12:20 pm
Offhand I'd think that these would be different areas that need to relate to each other, so they would work better if all in the same database. I'd definitely consider putting their tables in different schemas within the database though. It will help keep all the tables, views, stored procedures, etc. organized, and should make it easier to handle any database level security you may need to setup.
If you have separate databases for member, provider, insurance, you wouldn't be able to add foreign keys to your tables across those different databases so you'd have extra coding work to enforce referential integrity. Also, if you ever needed to do a restore, you'd have to take special consideration to ensure you restored ALL of those 10 databases to the same point in time to ensure transactional consistency.
July 6, 2018 at 1:04 pm
I don't know that I'd break things down along those lines per se... If you had vastly different security concerns for each of those areas, then maybe...
Speaking only for myself, if I were engaging in tyhis exercise, I'd be more inclined to break it up based on process type. For example, data that is hand keyed by users is typically irreplaceable. If that data is lost and/or corrupted there's little to no chance of everr getting it back. For that data you want your database in the full recovery model and you want regular backups (both ful and incrimental) being taken.
On the other hand, you're likely to have a great deal of data come in through "bulk feeds", either from insurance companies or government sources. The data itself is no less important than the "hand keyed" data but as long as you don't trash the original 3rd party data files you can, fairly easily, reimprort that data if something bad were to happen. In that case you could keep the database in the simple recovery model and take backups less frequently.
By separating the two functions, you keep the burdons of one function from unnessarily negativly impacting the other.
Just my 2 cents...
July 6, 2018 at 3:03 pm
Thank you all for your replies. I was concerned about in the off chance I was ever to move this to a medium size production system with many users that it may slow down the through-put.
The foreign key thought so great catch. Under this design there will be a lot of them in use.
I also appreciate the replies about backup/recovery. Although I have many many years in mainframe IT I am new to SQL Server and excited about playing in this new arena.
Thanks again for all your help. It appears that this forum will a huge help in SQL adventures.
July 6, 2018 at 9:28 pm
hcareinfosys.jgv - Friday, July 6, 2018 3:03 PMThank you all for your replies. I was concerned about in the off chance I was ever to move this to a medium size production system with many users that it may slow down the through-put.
The foreign key thought so great catch. Under this design there will be a lot of them in use.
I also appreciate the replies about backup/recovery. Although I have many many years in mainframe IT I am new to SQL Server and excited about playing in this new arena.
Thanks again for all your help. It appears that this forum will a huge help in SQL adventures.
Then let me introduce you to the "Swiss Army Knife" of SQL Server.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Also, some of the best advice that I can give to a newbie in the world of SQL can also be found in my signature line...
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2018 at 1:02 am
hcareinfosys.jgv - Friday, July 6, 2018 11:50 AMAs a way to keep my 72y/o brain stimulated, I am working on developing a C#/SQL server 2017 based health care system. My question is would I be better off creating separate SQL dbases for each of ten subsystems(member, provider, insurance, etc.
Thanks!
I suggest you start by doing a high level modelling of all the sub-systems and then the relationships between those. This will give you a clearer idea whether any of those sub-systems can be isolated or not.
😎
For this type of work, I mostly use either a column edit enabled text editor or an editor called yED, free and cross platform diagram editor.
July 10, 2018 at 4:10 am
hcareinfosys.jgv - Friday, July 6, 2018 3:03 PMThank you all for your replies. I was concerned about in the off chance I was ever to move this to a medium size production system with many users that it may slow down the through-put.
The foreign key thought so great catch. Under this design there will be a lot of them in use.
I also appreciate the replies about backup/recovery. Although I have many many years in mainframe IT I am new to SQL Server and excited about playing in this new arena.
Thanks again for all your help. It appears that this forum will a huge help in SQL adventures.
With reference to backup and recovery, you need to think about what would your recovery point objective (RPO) and recovery time objective (RTO) be. Once these are determined then a DR plan can be formulated.
***The first step is always the hardest *******
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply