October 25, 2002 at 7:13 am
Good morning Gurus!
We are trying to organize our data into separate databases but have the ability to link them up.
Example, we have in our organization, the hr dept, water services dept, admin dept, safety and purchase.
So what we would like to do is since each of these depts have one thing in common - employees, we decided to use empid as the generic primary key for all of the depts but we want each dept to have its own database.
In your expert opinion, how would you go about handling this?
How would you insert records, delete records, update records and view records in light of the fact that relational concept does not support this kind of design?
Thanks in advance
October 25, 2002 at 7:23 am
You can use a view to combine them all for reporting, nothing wrong with that. Biggest issue will be primary key generation. Even using SSN alone wouldnt work, potentially an employee could work for two departments, transfer from one to another. Combo of a deptid and ssn would work, or dept id plus an identity column. You could shortcut this some by just using the dbname as the department in the view.
Andy
October 25, 2002 at 10:06 am
Is there any specific reason that the different departmental data needs to be in separate databases?
The reason I ask is this: you might consider creating multiple schemas that are housed in the same database. Objects in databases have owners, but generally we like all objects to be owned by "dbo." But you can utilize object ownership as a means of creating multiple schemas within the same database. For example, you could create a schema for your HR department and have all of its objects owned by a user called "HR." The objects for your Financial department's schema could all be owned by a user called "Financial." Any objects that needed to be shared between the schemas, such as your tables with common employee information, could be owned by "dbo."
On reason we tend to have all objects owned by "dbo" is to avoid issues with permissions and ownership chains, but if each of your schemas is self-contained (i.e. all of the stored procedures, views, tables, etc., relating to a schema are owned by the same user), then this problem is largely alleviated. Additionally, each schema could be placed in its own filegroup, allowing you to isolate the different schemas to different disks and to backup the schemas separately (via FILEGROUP backups). By granting users from the various departments the ability to access objects that belong only to their department's schema (and any shared objects, such as employee information), you can maintain security and isolate the departments from one another.
This model has the advantage of allowing you to build the foreign keys you are looking for between department-specific objects and shared objects, such as employee information. It maintains most of the benefits of separating the different schemas in to separate databases, as well. Consider it as an alternative.
Matthew Burr
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply