Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Best Practices for Database Design Expand / Collapse
Author
Message
Posted Friday, March 25, 2005 1:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:54 AM
Points: 180, Visits: 687
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jgonzalez/bestpracticesfordatabasedesign.asp
Post #170175
Posted Tuesday, April 5, 2005 9:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
Why prefix views with a 'v'? Tables and views are identical as far as clients are concerned -- there is no reason to muddle your data model with that prefix.

Also, please define "reference". At what point does a "reference" table cease being for a reference? Can a non-reference table become a reference? Will you rename the tables at those times? You mention tables of two columns being reference tables. Can a three-column table ever be a reference? Four? What's the cutoff?


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #172251
Posted Wednesday, April 6, 2005 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 23, 2005 2:00 AM
Points: 1, Visits: 1

View:
Well,
a view simply isn't identical to a table, client or not,
and if you try to insert something into a view the error that will pop up will clearly
explain the difference.
so better mark them with a prefix so you can't mix it up ;o)

Reference Table:
I would call a table a reference table if it is i.e. a table which contains a n-m relation.
it just consists of foreign key references and maybe also some content fields,
but the main content is the n to m relation.
In my opinion the number of columns is irrelevant, but the relation by which the data
contained in the table is linked gives the context...

 

___

Markus Engelhardt
Web and Database Development
MCP

Post #172286
Posted Wednesday, April 6, 2005 1:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 PM
Points: 1,618, Visits: 1,548

I prefix all of my views with 'vw' and make the same requirement of the developers where I work. The main reason you would want to add a prefix is to avoid problems where developers try to give objects the same name. For example, if you have a table named 'promo_ref', you can't have a view named 'promo_ref' (unless you give it a different owner).

I'd hardly call adding 'v' or 'vw' to the name of views "muddling your data model". That's like saying that a penny is muddling Bill Gates' bank account.

What I don't do is use underscores in object names. It's not a rule, just a preference. Underscores slow me down when I'm typing.





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #172287
Posted Wednesday, April 6, 2005 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 8, 2010 4:05 AM
Points: 5, Visits: 9
For enterprise databases, an elementarian (role) should be appointed to manage a library of abbreviations that form the elements of database object names. The creation of a stable element library should occur in parallel with conceptual/logical modeling and before physical design.

Jeb Bushell

P.S. My favorite element abbreviation is rvnu for revenue
Post #172346
Posted Wednesday, April 6, 2005 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178

Naming standards are very good things. Common abbreviations are very good things.

Very good things can, however, be taken to silly extremes. We've got a logical modeling team tasked with, along with maintaining an enterprise logical model, establishing naming conventions. Unfortunately, these people work in the ethereal world of logical modeling, exclusively. They don't have to write code against the models they create, so naming every single table in a database Policy...* is not a problem for them even when we're talking about a couple of hundred tables, all inside a database named Policy. Establishing incomprehensible abbreviations like 'ddltbl' for deductible (do abbreviations generally add letters? note the new 'l') doesn't seem to slow down their work at all. The rest of us, dba's and developers, have been driven insane trying to get them to use an abbreviation like 'org' for organization instead of 'orgntzn'. I have, on more than one occasion, brought a dictionary over to their desk and pointed at common abbreviations defined with along with a word to no avail.

Don't get me wrong. I am in favor of practing a common approach. Just make sure the common approach makes sense.

Oh, and underscores in object names suck.

 



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #172363
Posted Wednesday, April 6, 2005 6:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 6, 2006 2:42 PM
Points: 48, Visits: 1

I agree with some of the ideas in the article, but I hate underscores in table field names.  Capitalizing each word within the name is just as readable and much easier to type (i.e., GrossAmountPaid).

And what's wrong with using a prefix and naming a table tblInvoices?

I try to avoid abbreviations unless their meaning is just as clear as the long version would have been.





Dana
Connecticut, USA
Dana
Post #172367
Posted Wednesday, April 6, 2005 6:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
By the way, the title of this article is misleading. I expected to see something about natural versus artificial keys, unique constraints, resolving sub-type roll-ups, logical arcs, denormalization by design, defining the cluster index as part of table design, establishing clustering strategies as part of database design. You know, database design. Instead it was a decent piece of work on establishing standards in naming. Not bad, just a surprise.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #172369
Posted Wednesday, April 6, 2005 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2005 6:36 AM
Points: 10, Visits: 1
You make several good points. I like a prefix on views so I know where to look in the database for the view/table. On some projects, I have also used tbl for tables, but this varies.

A lot of places require the use of stored procedures for data access, and use prefixes of sps_, spi_, spu_ and spd_ which potentially clashes with system procedures in SQL Server. Also, many times the stored procedure does an update or insert depending on what is necessary so spi_ and spu_ become confusing.
Post #172377
Posted Wednesday, April 6, 2005 6:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:46 AM
Points: 295, Visits: 280
This is no slight against J.D. but I have read more than enough naming convention articles To J.D.'s credit, he does present some interesting new ideas but this is one of those "religious" areas that could lead to centuries of discussion. Can we get a forum area

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Post #172379
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse