Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a question regarding the appropriate use of SQL Server database schemas and was hoping that some database gurus might be able to offer some guidance around best practice.

Just to give a bit of background, my team has recently shrunk to 2 people and we have just been merged with another 6 person team. My team had set up a SQL Server environment running off a desktop backing up to another desktop (and nightly to the network), whilst the new team has a formal SQL Server environment, running on a dedicated server, with backups and maintenance all handled by a dedicated team. So far it's good news for my team.

Now to the query. My team designed all our tables to belong to a 3-letter schema name (e.g. User = USR, General = GEN, Account = ACC) which broadly speaking relate to specific applications, although there is a lot of overlap. My new team has come from an Access background and have implemented their tables within dbo with a 3-letter perfix followed by "_tbl" so the examples above would be dbo.USR_tblTableName, dbo.GEN_tblTableName and dbo.ACC_tblTableName.

Further to this, neither my old team nor my new team has gone live with their SQL Servers yet (we're both coincidentally migrating away from Access environments) and the new team have said they're willing to consider adopting our approach if we can explain how this would be beneficial.

We are not anticipating handling table permissions at schema level, as we will be using application-level logins and the 7-character prefixes are not an issue as we're using LINQ so the tables can simply be renamed in the DMBL (although I know that presents some challenges when we update the DBML).

So therefore, given that both teams need to be aligned with one another, can anyone offer any convincing arguments either way?

share|improve this question
    
So is there a reason you are using Schemas in the first place? Do you need to segregate these functional areas for security? What's your motivation? –  JNK Dec 3 '12 at 14:08
    
Our motivation was to give us the option of controlling security this way further down the line, however this now seems less likely. Also, we felt that segregating our tables would be a good thing to do as there's nearly 100, and schemas seemed a neater way than purely using prefixes –  CrazyHorse Dec 3 '12 at 14:19
    
Well the biggest reasons to use them are portability and security. Absent that it's just personal preference. Maybe if you laid out your requirements more you could get a more complete response. Do you think you will need to move the accounting objects ONLY to another DB or server? Will you need to do security by functional area? –  JNK Dec 3 '12 at 14:33
    
No, I think it's unlikely that we will need to move any objects away from this server. So from what you've said it sounds like there is no "good" reason not to adopt their approach beyond my own personal preference. Thanks for your response –  CrazyHorse Dec 3 '12 at 14:50
    
I threw some of that into an answer. Let me know if you have other questions, but if you have deeper questions on database security or design Database Administrators may be a better place for it. I'm biased since I'm a mod there but we love questions like this. –  JNK Dec 3 '12 at 14:56
add comment

1 Answer

up vote 5 down vote accepted

This is far from an authoritative answer on using schemas, but generally you want to use them for one of two reasons: Security isolation or portability of related objects.

Security

It's possible to assign security permissions based on schema, which greatly simplifies complicated structures. Think of an HR system where some people need to see hours, managers need to see files, but only specific individuals need to see anything related to pay. If you put the pay stuff in a Salary schema you isolate from the rest of the data pretty effectively.

This can be done separately with other methods like views or granting permissions to specific objects but if you want to separate a group of related objects this is an easy way to go.

Portability

Some folks believe schemas make objects more portable. Continuing our previous example, if you need to put the Salary data and related objects into another database or on another server, if they are entirely contained in that schema it would (potentially) be less work to move them all since you script out all objects in that schema.

In practice if you stick to a naming convention you can get the same effect without using a schema.

So, if you don't have security concerns with these objects, and you don't think you will ever need to move them (and you are going to use a strict naming convention ANYWAYS) then it's really a toss up as to which would be preferred.

share|improve this answer
    
Thanks, that's a very useful answer. I can at least now raise these arguments in our discussions –  CrazyHorse Dec 3 '12 at 15:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.