dim schema or DimTableName

View previous topic View next topic Go down

dim schema or DimTableName

Post  rquinn on Fri Feb 22, 2013 7:07 pm

Hi, I came across a post the other day on http://arcanecode.com/, it's about date dimensions but my question is about a naming convention talks about (briefly). he uses a "dim" schema so tables are dim.Date rather than dbo.DimDate or whatever.

I hadn't really thought about using this approach, but it's appealing. I'm working with a group right now, who still uses a lot of SQL and all the dim_customer and dim_date seems tedious.

I know they should be using a UI.

anyone have any strong negatives around using schemas, or what are people doing? not even bothering with dimTableName and just go with tablename. interested to hear.

thanks for reading

robert

rquinn

Posts : 3
Join date : 2013-02-22
Location : NJ USA

View user profile http://www.linkedin.com/in/rdq123/

Back to top Go down

Re: dim schema or DimTableName

Post  ngalemmo on Fri Feb 22, 2013 7:53 pm

Seems like overkill. And, who exactly, is a set up like that supposed to benefit? Certainly not the users.

I've been at this for a long time and I could never figure out what the benefit was of elaborate naming schemes to classify tables. Maybe its inherent in our nature as modelers. But it seems to me, a decent modeler should be able to tell the difference between a fact and dimension table by looking at it. I like to use names like: Dates, Customers, Products, etc... That should be enough to know its a dimension.

Their can be technical reasons in extreme cases to distribute tables into different schema, usually to make backup and restore easier for very large tables, but you usually wind up building a 'user' schema with synonyms and views so the whole looks like one environment.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: dim schema or DimTableName

Post  rquinn on Sun Feb 24, 2013 9:10 am

Thanks, that makes sense. I like the simple names approach as well, but I've seen a lot of examples of prefixing with "dim". Usually there's a UI on top and no one sees these names (it shows simple descriptive names), but because I'm back in SQL the prefix stands out as noise.

rquinn

Posts : 3
Join date : 2013-02-22
Location : NJ USA

View user profile http://www.linkedin.com/in/rdq123/

Back to top Go down

Re: dim schema or DimTableName

Post  Jeff Smith on Mon Feb 25, 2013 11:43 am

I don't get it either. Isn't the benefit of using different Schemas is it allows easier security controls? Is there any other benefit? Because if not, I don't see how seperating fact from dimensions by schema has any benefit.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: dim schema or DimTableName

Post  rquinn on Mon Feb 25, 2013 12:47 pm

Schemas have many features/benefits, with security being a primary use case, but at it's core it's a name space so not restricted to that.

rquinn

Posts : 3
Join date : 2013-02-22
Location : NJ USA

View user profile http://www.linkedin.com/in/rdq123/

Back to top Go down

Re: dim schema or DimTableName

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum