Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2003-01-24 : 12:11:26
|
| Hi,I'm writing an insurance rating application for multiple companies. Each company has different insurance rates etc. We were thinking of seperating each company's rates etc. into seperate databases. The main rating database would retrieve a list of available companies from its meta data and then retrieve the rate data from the seperate databases. Some of the results from the different databases would be have to be combined. (i.e. result sets from multiple databases.)Ideally it would be great if we could write views from the main database that accessed the company databases. Has anyone done this? This would be the preferred method as opposed to the Application (C#) retrieving results from all the seperate databases and merging the data etc in the application.Is is possible to write views/sprocs that access seperate databases? Will communication between datbases add alot of overhead?The seperate database idea is nice since we can manage each companies rates seperately.I know these are vague questions but I just want to see if this idea is feasible.ThanksNic |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-24 : 12:21:57
|
| Me personally, I like single database solutions. It's easier to maintain changes on one database compared to a change to EACH of your customers database. Backups are more involved because you've got sooo many databases to keep track of. Everytime you add a new database, your administrator is going to have to know to add that database to the list of databases to backup. It'd be much easier if there were just one database, one backup, and adding records (not databases) for new customers. Cross database communication can be a bit slower and sometimes a secrity nightmare.If you design your database / tables correctly, each customer can easily have their own set of rates.For example:You could have a Customer Table which has a relationship to CustomerRates table. The CustomerRates table would have a relationship to the Rates table. That way, each customer could have N rates.Easy to query, and much easier to impliment and deal with vs a database for each customer. Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-24 : 12:35:24
|
I agree with Michael on this, see this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15849I've seen a single sales tax database designed in such a way that it could handle every possible sales tax variation, and if sales taxes can be done that way, then insurance rates should be a piece of cake.  |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-24 : 13:38:52
|
| Wow Rob, thanks for the compliment :)Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
communicate with other databases