Use of schema names

[Mike Lewis]

I'd be grateful for some advice on the use of schema names. Are they a good idea? Or are they more trouble than they're worth?

Just to be clear, by "schema name" I mean the third element in the four-part naming convention. For example, in AdventureWorks, there is a table:

MyServer.AdventureWorks.[b]HumanResources[/b].Department

In this case, HumanResources would be the schema name.

I am about to start work on desigining a new database (with about 200 tables). The data logically falls into five sections, corresponding to the five departments of the company that will be using it.

There are no issues with permissions. All end-users will have access to all parts of the database. All access will be through a desktop application.

I can see the advantage of of splitting the database into five logical sections. It will be nice to be able to see at a glance which area of interest a given object belongs to; and I like the idea of being able to filter the Object Explorer in Management Studio.

On the other hand, it seems like extra work to have to constantly prepend the schema name to every object in the SQL code.

So what's the general opinion? Are there any advantages other than those I mentioned above? And, if so, do they outweigh the extra effort involved?

Thanks in advance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Rhys666]

I tend toward the idea that they can be hugely practical if planned and used appropriately. It does help with the idea of separating concerns away from each other and I would suggest that is actually good practice to always 'prepend' the schema name in tsql anyway with the move from ownership to schemas.

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[Olaf Doschke]

At another forum I recommended a user to use schemas for hosting the same database for multiple customers, each of them would just see one schema, set as his/her default and so you could reuse the same table names within the same database of the same instance via seperation by customerschemas.

But the idea to use schemas for the departments also is a valid use of the schemas, of course, as your example shows. You still have the option to create seperate databases, if it comes to mandant usage of that database with the concern to seperate data.

So overall: Yes, go for that.

Also see here for a bit of discussion about schema usage:

http://stackoverflow.com/questions/529142/what-good-are-sql-server-schemas

Bye, Olaf.

 

[Mike Lewis]

Rhys and Olaf,

Thank you for both for your helpful advice. I was tending towards using schema names, and your replies have confirmed that I was on the right track. I will discuss this further with the client, but I'll probably suggest we go ahead with it.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy