Database Naming Convention

  • I have seen lots and lots of commentary and comments about naming SQL Server database objects. But what about the name of the database itself? Is there a definitive best practice standard for the naming of a relational database? Should the name be limited to a few characters? Should it be long and verbose? Somewhere in-between?

    I would appreciate any thoughts everyone might have on this topic.

    Thanks

  • I've never seen any convention on this. It's best to give a DB a short meaningful name with no spaces. Sometimes DB names may be for example suffixed with '_DEV', '_TEST' etc if you've got development databases on the same server. If you've got a number of DBs with very similar names it can be confusing so avoid this.

  • laurie-789651 (7/31/2013)


    It's best to give a DB a short meaningful name with no spaces.

    I agree with that. Make the database meaningful or even playful if you want. Spaces in the db name are a pain but they can be used in the db name.

    The key is that you know what the database is and represents.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Most would probably say it's overkill, but I prefix my OLTP databases with db, my staging areas with sa, and my OLAP database with dw. The names are otherwise short and clear.

  • laurie-789651 (7/31/2013)


    I've never seen any convention on this. It's best to give a DB a short meaningful name with no spaces. Sometimes DB names may be for example suffixed with '_DEV', '_TEST' etc if you've got development databases on the same server. If you've got a number of DBs with very similar names it can be confusing so avoid this.

    What an utterly horrible idea. So when my content_music and content_general databases go into production the system fails miserably because all the testing was done with SPs in content_general_test (whose name is changed to content_general in the release process) referring to tables in content_music_test (whose name is changed to content_music in the release process, so that content_music_test exists only on the test system, not on the production system).

    OK, it's not likely to happen, if your release processes are reasonably professional. But I would strongly recommend not giving it the slightest chance to happen, and sticking "_TEST or _DEV or other postfixes on the database name in various stages of design, development, test, system test, pre-release validation, everything before release is just giving this particular failure a chance to happen in production.

    Tom

  • So what do you all think about database names as short as 5 characters in length? Which is a standard that is mandated where I work?

  • Oops, sorry. The mandated standard here is 4 characters vice 5.

  • Nemeaux (7/31/2013)


    So what do you all think about database names as short as 5 characters in length? Which is a standard that is mandated where I work?

    Sounds crazy to me, for anything I've worked on. But I imagine there could be contexts in which it makes sense. So is where you work such a context? If so I'd love to hear about it, because I'm always interested in discovering things that are strange and new to me.

    Tom

  • I really don't think where I work is such a context. I can't really imagine limiting "production" database names to 4 characters in any context. Something about defeating the self-documentation aspect of longer names eludes me.

    By the way, I didn't make nor do I subscribe to the aforementioned standard. I'm just searching for a rationale for the shorter database names, aside from laziness in referencing a database name in code.

  • Clarity, clarity, clarity.

    You need to make things clear. Keeping the names arbitrarily short does nothing but obscure meaning. You want to know what things are, quick, clear and simple. In fact, this should be your naming standard for everything. Keep things clear.

    I'm also completely against naming databases based on the environment they are in. That makes things EXTREMELY difficult to manage for testing, coding, rollbacks from production, etc.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The mandated standard here is 4 characters vice 5.

    I have some short names. If I remove my two letter prefix I already outlined (db, sa, dw) most would fit the 4, but without someone giving a compelling reason, that seems to me foolishly arbitrary. Even DOS let you have 8 characters, a limit which, while still somewhat arbitrary, is at least more reasonable.

  • RonKyle (7/31/2013)


    Most would probably say it's overkill, but I prefix my OLTP databases with db, my staging areas with sa, and my OLAP database with dw. The names are otherwise short and clear.

    Why? What do you get out of it that a clear name doesn't give you?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Nemeaux (7/31/2013)


    So what do you all think about database names as short as 5 characters in length? Which is a standard that is mandated where I work?

    Same question, why? What does that accomplish.

    Oh, and I've worked on servers with a couple of hundred databases. 4 characters each... what a nightmare. A friend is working on one with 2500 databases. Again, 4 characters will do... what exactly for you?

    Seems like a sure path to confusion and difficulty. Standards like this don't help anyone, they get in the way and slow things down.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • RonKyle (7/31/2013)

    --------------------------------------------------------------------------------

    Most would probably say it's overkill, but I prefix my OLTP databases with db, my staging areas with sa, and my OLAP database with dw. The names are otherwise short and clear.

    Why? What do you get out of it that a clear name doesn't give you?

    It allows reuse of the same name. All of the OLTPs have a corresponding staging area only for them. After the initial processes they are sent to centralized staging area so the data can be conformed across all incoming data. That final staging area and the OLAP data warehouse database also have the same same. So if I have two OLAP databases called CARS and FINANCE and my company name is KYLE, you would see the following database names.

    dbCARS, whose staging area is saCARS. The sa is a further indication that the database is NOT querable (per Kimball). dbFINANCE has saFINANCE. Both sa s feed into saKYLE, which conforms the data and in some cases puts data in 3NF where necessary. Finally the data feeds into dwKYLE.

    The dbs are not on the same server as the sa and dws but I think it makes it easier to see the flow, and on the OLAP server groups the databases nicely. I do have a dbETL on that server, which stores all the information about the ETL processes. As it has a db prefix, it is a querable OLTP database.

  • RonKyle (8/1/2013)


    RonKyle (7/31/2013)

    --------------------------------------------------------------------------------

    Most would probably say it's overkill, but I prefix my OLTP databases with db, my staging areas with sa, and my OLAP database with dw. The names are otherwise short and clear.

    Why? What do you get out of it that a clear name doesn't give you?

    It allows reuse of the same name. All of the OLTPs have a corresponding staging area only for them. After the initial processes they are sent to centralized staging area so the data can be conformed across all incoming data. That final staging area and the OLAP data warehouse database also have the same same. So if I have two OLAP databases called CARS and FINANCE and my company name is KYLE, you would see the following database names.

    dbCARS, whose staging area is saCARS. The sa is a further indication that the database is NOT querable (per Kimball). dbFINANCE has saFINANCE. Both sa s feed into saKYLE, which conforms the data and in some cases puts data in 3NF where necessary. Finally the data feeds into dwKYLE.

    The dbs are not on the same server as the sa and dws but I think it makes it easier to see the flow, and on the OLAP server groups the databases nicely. I do have a dbETL on that server, which stores all the information about the ETL processes. As it has a db prefix, it is a querable OLTP database.

    I'll be honest, I'm confused. I mean if it works for you, great. But why not FinanceStaging or Cars (for the readable/OLTP) or KyleDataWarehouse. Those can be the same as what you have, but two things are done, the sorting is by the name of the database, not by type, and it's clear. Very little training is necessary to let people know what each thing does and you don't have to worry about accidently connecting to dwKYLE when you wanted dbKYLE since the second letter gets a little lost in there... Again, not trying to throw rocks or start a fight. I'm just a HUGE fan of clear and simple language in order to reduce friction and overhead so I advocate for it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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