Sql-server – When is it appropriate database design to use a separate schema

database-designdb2oraclesql server

I am developing a database which has data sourced from many different applications. In my first pass at design, I placed the staging tables each in a schema named for their source application.

As several of the source applications have similar data and similar table names, I use the schema name to differentiate the source application. The alternative I am considering would be using a single schema and including the source application in the table name.

I wanted to look into the design rules pertaining to when to use a different schema and the pros and cons of doing so and I could not find anything.

Is the schema purely for permissioning and security?

Does it make sense from an organizational point of view to create objects in separate schemas beyond what is required for application development or is this just needlessly adding complexity to queries?

Are there any other repercussions of this decision which I have neglected to consider?

Best Answer

From the description you gave I don't think security is the concern here. As you said your application receives data from multiple sources. The security comes/implemented when you have role based security kind of things on retrieval/access methods of that data. I am guessing the there must be first some kind of consolidation, analysis, reconciliation etc... happening before the data is is being presented to user.

In such cases separate schema is the best way to keep source data intact,not only during staging but even a original copy of source data; Just in case you want to troubleshoot later what data has been received from specific source.

Depending on what are the data providers but if this is financial data it is very likely the provider update source data format, add/remove columns etc.. frequently. if multiple source starts doing this at the same time it will be difficult to manage those changes if you have one consolidated schema design. but if you had seperate schema you know that this changes is not going to affect other sources data.

Second scenario if you are expecting you application as a source providing Some sort of consolidate data or source specific data to another subsystems then with separate schema you have more control and flexibility it terms of how and what you want to share. If you have same schema; sharing data will becom pain by Selecing some tables, or custom roles, or writing alternate APIs.

Well down side of having seperate schema is you have to use fully qualified object names to a life any unexpected results. Also if you have any scenario wher you might use open/distribute queries, linked servers, etc... Remember that it allows only two level of object name resolution. Think about other things like SQL agent jobs, and other features you might be using and check it supports fully qualified object name resolution or not.

Related Solutions

MySQL Database Design – One Big Database vs Several Smaller Ones

I ran a system with the best part of a thousand databases, spread across multiple servers. They were all an identical structure and were synchronised with a template database which was on each of the machines.

This allowed me the ability to migrate databases from one db to another if one was getting excessively over-loaded, and as the client mix changed, I could create new databases on different servers to load balance across the servers. This was the biggest advantage I got from the system, in that I had multiple large lumps of tin performing multiple complicated queries simultaneously on the separate servers.

The great thing about this, is that you can add servers to the configuration at your own speed, as each server starts to get over-loaded, add another into the mix, migrate some dbs across to the new server and end up with a nicely load balanced set of servers. A really nice and simple way to add scale to the system as and when it is required!

The reason I went with this approach rather than the single huge database approach, was the sheer size of the potential database that would have been created... each of the 1000 databases had 200 tables, and many of the individual tables within each of the databases comprised many hundreds of millions of rows of data!

A single database configuration would have required certain tables (approx 8 of them) to have multi-billions of rows of data, and the total db size would have been over 10Tb. We were able to have multiple servers with 5Tb of RAID 10 storage, with many databases on each.

That's what I would do! Hope it helps your decision making... :)

Database design with different user roles connected by different relationships to one organizaiton table

What are the pros and cons of above schema?

The major con that I see is that freelancer and employee have the same fields, but are different tables. The difference is what primary_key is composed of, but I wonder if you could normalize this as

emplpoyee
{
    user_id,
    employee_type, (could be "FREELANCER" or "FULLTIMEEMPLOYEE")
    organization_id,
    Primary_key
}

For primary_key, you might need some application logic to create a string based on user_id and employee_type, and optionally include tha value of organization_id if the employee is a freelancer.

You could even go a step further and have a table employee_type:

employee_type
{
    id,
    description
}

and then reference that ID in employee.employee_type. This would deal with your problem of adding new tables every time there is a new type of user.

Also, Have you considered merging employee with user? This would simplify your schema even more, but you would lose the ability to have a user_id associated with multiple employees, which I think is important for this case.

Best Answer

Related Solutions

MySQL Database Design – One Big Database vs Several Smaller Ones

Database design with different user roles connected by different relationships to one organizaiton table

Related Question