Best Practices for Database Design

  • jdgonzalez@jdandrachel.com

    Hall of Fame

    Points: 3161

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jgonzalez/bestpracticesfordatabasedesign.asp

  • Adam Machanic

    SSCoach

    Points: 15259

    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
    whoisactive

  • Markus Engelhardt

    SSC Enthusiast

    Points: 121

    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

    One Orange Chip

    Points: 28027

    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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • jeb bushell

    SSC Journeyman

    Points: 87

    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

    SSC Guru

    Points: 395634

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • DanaH1976

    Ten Centuries

    Points: 1026

    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

    SSC Guru

    Points: 395634

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Eric Weiss

    SSC Enthusiast

    Points: 142

    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

    SSCertifiable

    Points: 7824

    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

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Scott Arendt

    SSCertifiable

    Points: 7671

    You can argue all day about naming conventions.  But I think that the main point is too decide on a convention, document it, and enforce it. 

    Regards,

    Scott

  • Tatsu

    SSCertifiable

    Points: 7824

    Exactly Scott! It's OK to present new ideas and use other people's ideas to improve existing standards as well. I don't think there are many people in this community who would argue that standards are not beneficial. It is important to communicate new ideas but I don't think it warrants article status until we can identify a good set of standards that at least 80% of the active community can agree on. I don't see that happening

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Adam Machanic

    SSCoach

    Points: 15259

    INSTEAD OF triggers are there to support view updatability.

    So any table in an n-m relationship is a reference table? Does every table in your database with that kind of relationship have that prefix/suffix?

    --
    Adam Machanic
    whoisactive

  • DanaH1976

    Ten Centuries

    Points: 1026

    Regarding the post "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."

    I use the prefixes upd_, ins_, and del_ for stored procedures that perform update functions.  To get around the conflict with system procedures that start with sp_, I use sel_ for simple selects.  This is very clear even to a non-programmer, and makes it easy to find procedures by their function.

    Now if only Enterprise Manager would let us save sprocs in folders so we didn't have to scroll up and down a list with thousands of entries!



    Dana
    Connecticut, USA
    Dana

  • DanaH1976

    Ten Centuries

    Points: 1026

    For reference tables, I use a suffix of "Lookup".  Had I to do it over again, I would have used a special prefix instead so that the lookup tables would sort as a group.  However, the suffix does make the function of the table very clear. 

    Almost all of our lookup tables have two fields (three at the most), usually just a key and a description.  If the table has any function other than a lookup, it's not considered a reference table.



    Dana
    Connecticut, USA
    Dana

Viewing 15 posts - 1 through 15 (of 146 total)

You must be logged in to reply to this topic. Login to reply