Geeks With Blogs
Eron Wright - All Killer No Filler blog

Hello, another thread compelled me to spell out what my supposed state-of-the-art thinking is on database object names.

Sql Server has no concept of a namespace for object names.  That is, all names for tables and views (and more) must be unique.  Well, there is in fact a single namespacing concept - the owner! 

We create owners for the logical namespaces in our product.  The views and (primarily) computed tables that belong to that namespace will be owned by the appropriate sql account.  In that way, intra-namespace calls between relations need not be qualified, but inter-namespace calls must be. 

Also, the same relation name can be used by various subsystems, as they implement their views.  This avoids the ugly problem of assigning arbitrary suffixes to view names.  For example, consider possible view names over some table "dbo.Equipment" - "dbo.EquipmentSummary", "dbo.EquipmentView", "dbo.EquipmentDetails".  OUCH.  I prefer “[MyProduct.Admin].Equipment“, “[MyProduct.Search].Equipment“.

The application does not login to sql using these accounts - yet.  In time, we might do this and finally be able to take advantage of sql server's security mechanisms.

It makes sense in a twisted way, hey?  The namespaces "own" the relations!  Of course, triggers and procedures are also treated in this way.  It's helpful when a certain base table has many orthogonal triggers that those triggers are owned by the subsystem that requires them.

Note that, within Enterprise Manager, you will sort by Owner, not Name, to get a nice subsystem-centric view of your db objects.

We are early into the implementation of this approach.  Please give me your feedback/concerns, thanks!

 

Posted on Saturday, November 8, 2003 10:30 AM Technology | Back to top


Comments on this post: SQL: Use object owner for namespacing object name

# re: SQL: Use object owner for namespacing object name
Requesting Gravatar...
Why not just use qualified names?

select * from [MyProduct.Admin.Equipment]

or

select * from MyProduct_Admin_Equipment


Left by Kenneth Brubaker on Nov 12, 2003 8:08 AM

# re: SQL: Use object owner for namespacing object name
Requesting Gravatar...
The reason to use the owner rather than a name with periods is that you can use the security features of SQL Server. Also, an object can refer to another object (with the same owner) without owner-qualifying the reference.
Left by Eron Wright on Feb 29, 2004 5:48 AM

# re: SQL: Use object owner for namespacing object name
Requesting Gravatar...
The Yukon team mentioned something very interesting: the "schema" concept is implemented in Yukon. Today all database objects have owners, which we think of as accounts. In fact, they have "schemas", where one schema exists for each user account. The schema takes the name of the user. The point is that, in Yukon, a single user can own several schemas.

I think this functionality nicely fits my recommendation of using owner (actually schema) to namespace objects.
Left by Eron Wright on May 03, 2004 8:51 AM

Your comment:
 (will show your gravatar)


Copyright © Eron Wright | Powered by: GeeksWithBlogs.net | Join free