4

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?

5
  • 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
    Commented Dec 3, 2012 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
    – Niall
    Commented Dec 3, 2012 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
    Commented Dec 3, 2012 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
    – Niall
    Commented Dec 3, 2012 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
    Commented Dec 3, 2012 at 14:56

1 Answer 1

Reset to default
6

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.

1
  • Thanks, that's a very useful answer. I can at least now raise these arguments in our discussions
    – Niall
    Commented Dec 3, 2012 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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