June 2, 2009 at 3:40 am
I have an application that I am converting to SQL Server. Presently I have multiple DBs around the functional areas like Insurance, Safety, etc., and a common DB for Suppliers, users, etc.
Does it make sense to do this in SQL server? I would have stored procedures that would have to reach across DBs and "should" really have relationships (which I don't have now) across these boundaries.
I see schemas which can separate areas. Is this the more common method?
Fred
June 2, 2009 at 4:16 am
Hi FredS
Its really a matter of business knowledge of how your company will use the data.
I did a lot of work in the insurance industry, and a common factor seemed to be separation of 4 business area.
Quotes
Policies
Claims
Back office accounting
In all systems I've come across, the back office accounting was always in a separate database to the business, sometimes a separate application. Some combined quotes and policies into one database.
One company had further separation of quotes into business type - household, motor, commercial etc - but not a similar separation for policies. Some separated commercial from personal lines - because things like shipping/cargo are very rarely going to be used in the same context as private car insurance.
There were some cross database queries and stored procedures, but the clearly defined business boundaries meant these were also obvious business processes, like converting a quote to a policy or collecting the cash from premium.
But commercial underwriting & re-insurance can be a real nightmare, as insurers seem to jump through hoops to make the underwriting process as byzantine and mysterious as they can.
Hope this helps.
PS if are you able to disclose any of the insurance company names involved - I may have had prior dealings.
June 2, 2009 at 5:29 am
Our back office functions are completely separate.
What we do is track insurance certificates, safety history, and various signed documents for suppliers to ensure compliance with contract terms. We do this as a service as opposed to a self-service website, etc.
In that light almost everything is related to the same thing. Suppliers have certs, terms sign-offs etc.
Fred
June 2, 2009 at 5:59 am
Separation of databases is usually along the lines of business function. If you only have one business function, then one database may be a suitable option. If you have clearly defined sets of users doing different operations on different data, with minimal overlap, then multiple databases may be more sensible. Could part of the business continue working if one of the databases were offline - or is all of it required?
SQL Server 2005 can span databases across filegroups and partitions, Schema and Users are now separated, so now its even easier to have one large database with multiple functions.
There are many considerations & depencies:
disk space
volume of data you have now
anticipated future expansion
recover mode
backups -
disaster recovery
Ease of use from a SQL Server perspective: Its probably simpler to maintain several databases with simple structures, than one database with multiple filegroups partitioned over several drives
So no simple answer I'm afraid.
June 2, 2009 at 6:25 am
Thanks. It looks like one for now.
Fred
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply