SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practices for Database Design


Best Practices for Database Design

Author
Message
jdgonzalez@jdandrachel.com
jdgonzalez@jdandrachel.com
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 757
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jgonzalez/bestpracticesfordatabasedesign.asp
Adam Machanic
Adam Machanic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 714
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
Markus Engelhardt
Markus Engelhardt
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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


Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2870 Visits: 1623

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 MVP
Database Engineer at BlueMountain Capital Management
jeb bushell
jeb bushell
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41835 Visits: 32666

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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
DanaH1976
DanaH1976
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41835 Visits: 32666
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Eric Weiss
Eric Weiss
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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.
Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 307
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search