Database Schemas

time to read 2 min | 351 words

I was asked to comment on the use of DB schemas, so here it is. The first thing that we need to do is decide what a schema is.

A schema is an organization unit inside the database. You can think about it as a folder structure with an allowed depth of 1. (Yes, just like MS-DOS 1.0). Like folders in the real file system, you can associate security attributes to the schema, and you can put items in the schema. There is the notion of the current schema, and that about it.

Well, so this is what it is. But what are we going to use if for?

People are putting schemas to a lot of usages, from application segregation to versioning. In general, I think that each application should have its own database, and that versioning shouldn't be a concern, because when you upgrade the application, you upgrade the database, and no one else has access to your database.

What we are left with is security and organization. In many applications, the model layout naturally fall out into fairly well define sections. A good example is the user's data (Users, Preferences, Tracking, etc). It is nice to be able to treat those as a cohesive unit for security purposes (imagine wanting to limit table access to the Accounting schema). It is nice, but it is not really something that I would tend to do, mostly because, again, it is only the applications that is accessing the database.

Defense in depth might cause me to have some sort of permission scheme for the database users, but that tends to be rare, and only happen when you have relatively different operation modes.

What I would use schemas for is simply organization. Take a look at Rhino Security as a good example, but default, it will tack its tables into their own schema, to avoid cluttering the default schema with them.

In short, I use schemas mostly for namespacing, and like namespaces elsewhere, they can be used for other things, but I find them most useful for simply adding order.