June 16, 2003 at 6:48 am
I'm curious what type of design recommendations anyone would have under the following circumstances. We have come up with several solutions, none of which are perfect. The design we are currently considering is as follows:
We have two distinct companies but many operations overlap and both are in the same industry (transportation). Most users need access to data from both companies, but not in all cases. We have debated about creating a Common database that would contain tables such as Companies, States (AL, PA, etc.) and other commonly used tables. We would then have two Master databases named after each company that would contain tables like Trucks, Drivers, Customers, etc (This would allow us to have only one location to make modifications or additions to). Each application then would generally have it's own database with tables that access data for each company generally specific for that application. Each table would have the same schema to aid in reporting (Crystal Reports) although some fields for one company may have all NULL values.
All data access would be done through stored procedures.
One of our concerns is keeping referential integrity across databases, since many of the tables in the application databases would reference both the Master Company databases and the Common database.
If anyone has a better design or suggestions, I'd like to hear them. Thanks.
Tom
June 19, 2003 at 8:00 am
This was removed by the editor as SPAM
June 19, 2003 at 8:29 am
quote:
One of our concerns is keeping referential integrity across databases, since many of the tables in the application databases would reference both the Master Company databases and the Common database.
If you need to maintain referential integrity across databases, you'll either have to do it in the application or with the use of triggers. That should enter into your design consideration.
We have a similar problem where I work in that we support some 20 different organizations that share the same lookup tables, etc. What the development DBAs are now doing are creating views with row-level security. All the data is in one database and there is only one set of objects, but the view filters the data based on something identifiable (such as a username associated with the organization).
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply