General discussion

  • Creator
    Topic
  • #2255697

    Database Naming Standards

    Locked

    by mattohare ·

    In the late 80s, the only naming standard I saw for database objects were vague ones like ‘tbl’ for table, ‘qry’ for query. I noticed that these were pretty useless when you would get a list of about fifty tables and a few hundred stored procedures.

    For tables, I started using things like ‘Profile’ for major single objects, ‘Lu’ for Lookups, ‘Trans’ for transactional tables, ‘Bfr’ for temporary buffers. For stored procedures I’d use ‘sel’ for select, ‘upd’ for update, etc.

    When my stored procedures got into the hundreds, I added a single letter to the beginning to represent business area (‘p’ for property/real estate, ‘i’ for investor, etc.).

    Has anyone else come up with any naming convention ideas for larger projects? I’m getting to the point where I’m going to put a business tag on my tables, I think.

All Comments

  • Author
    Replies
    • #2538252

      Database naming standards

      by mbeards ·

      In reply to Database Naming Standards

      Here is a link to an Oracle article on guidelines. It’s more for the underlying infrastructure naming standards. It’s a bit old but still relevant: http://members.ozemail.com.au/~gpiper/oracle/ora_8.html
      (Oracle part number is A-19308-1 Written by Cary V.Millsap Oracle Corporation)

      I’ve documented what we do at my company and it ended up being a 5-page document. But here a few things we do.

      For the actual database objects, I’ve used the first one or 2 positions of the object’s name to tell what it is, followed by an optional digit if there are more than one of the same type of object, and the rest a short description of what the purpose of the object is. Depending on what database engine you’re using, the length of the name may be limited.

      For example, TBPAYROLL, is the Payroll table.
      X01PAYROLL is a regular index on the payroll table, PKPAYROLL is the primary key on the payroll table. TG is used to signify a trigger and added U, I, D, for update, insert, delete. And then a B or A for before or after trigger. Example, TGUBPAYROLL.

      When you add the schema name to things that helps to further clarify things. Oracle and DB2 identify things that way, SCHEMA.TABLE_NAME or SCHEMA.INDEX_NAME, etc.

      • #2538646

        You are doing it wrong

        by jcelko212 ·

        In reply to Database naming standards

        >> For the actual database objects, I’ve used the first one or 2 positions of the object’s name to tell what it is, followed by an optional digit if there are more than one of the same type of object, and the rest a short description of what the purpose of the object is. Depending on what database engine you’re using, the length of the name may be limited.

        For example, TBPAYROLL, is the Payroll table. << Please read the ISO-11179 Standards; this approach is the way we did things in FORTRAN and BASIC decades ago. A data element name tells us what something is in the logical model. NOT its datatype, NOT its storage location, NOT its implementation. Names need to be in a "_” format.

        The “TB-” prefix screws up the data dictionary and it is just plain silly. SQL has one and only one data structure — the table (base, derived, or virtual).

        Do you know about the error for reading uppercase only text? This is why newspapers and books use mixed case.

        Finally “Payroll” begs the question, “payroll what?” Checks? Accounts? Domestic?, Foreign? My favorite exmaple of that kind of vagueness is to answer the question “sex:” on a form with “Yes” or “~120 times” just mess them up.

      • #2525652

        All tables start with T then?

        by mattohare ·

        In reply to Database naming standards

        This sounds a bit like the naming standards I saw in some MS-Access databases. tbl for tables, qry for queries, etc. The problem is that a list of tables doesn’t get much of a structure. If I have a couple hundred tables on a list, I’d like to have the first letter different, for a start.

    • #2538745

      Simple but effective solution

      by c_tharp ·

      In reply to Database Naming Standards

      Here is a simple solution that can be applied to most databases.

      Above all else, use names that are meaningful to the application and the KISS principle. In general, think in terms of
      things and actions on things. Name things with nouns and actions with verbs.

      Names should be singular. A table holds many records, but they are used one at a time. If a table name describes one
      record, references to columns will read better (i.e. agent.name). Records are acted on one at a time, so procedure names
      should be for a singular action. Plurality does not add anything and interfers with code readability.

      Prefixes or suffixes can be used to group objects without making names extremely long (i.e. “f” for finance or “s” for sales). Sometimes, subgroups are useful (i.e. “ar” for accounting receivables and “ap” for accounting payables). Using and underscore (“_”) after a prefix or before a suffix makes them stand apart from the name. Thus, the prefix or suffix does not need to be a fixed length to define it.

      Everything else that is put into the name identifies the type of object or restricted use.

      The context will usually identify the type of object to a programmer. Identifying the object in the object name usually states the obvious. For example, “select name, street, city, state, postal_code from customer, address where fk_customer =
      customer.pk_id”. Putting “col” in name, street, city, state, or postal_code adds nothing. Likewise, putting “table”, “ta”,
      “tb”, or some other code in name or address provides nothing. The context identifies the columns and the tables. The prefixes, “pk” for primary key and “fk” for foreign key, identify the columns according to use. This kind of key idenitification should only be used if the columns are constrained as keys. In this case, there may be multiple addresses for each customer. The primary key is unique. The foreign key is not unique but must exist in the parent table. Composite keys could be identified with a prefix of “ck”.

      Packages, procedures, triggers, and sequences should be documented so that a programmer will know how to use them without reading the code. Therefore the name does not need to identify the type of object. A package is a group of objects and should be named with a noun. Procedures and triggers take action, so they should be named with a verb. A sequence is a single type of action that could be named with a verb, though in use it appears more like a column, so it is simpler to use the code, “seq”, in a noun. Codes for insert, update, or delete may be appropriate in simple procedures. Procedures that operate on multiple tables in varying modes should not be identified so narrowly.

      Examples
      table: customer
      column: name (with schema it becomes customer.name)
      procedure: bill_for_sale
      trigger: invoice_add_timestamp_bi (invoice is the table name; “bi” suffix indicates action before insert)
      Since there can only be one trigger before insert this could be shortened to sales_order_bi, but that makes the code less readable. The less descriptive form is more appropriate if many tasks are being prefomed.
      sequence: shipment_seq

      Of course, names alone will not provide enough information for a programmer. An ERD and procedure documentation are
      necessary. Good names simply make the code easier to understand.

      • #2538640

        Almost, but not right — your mindset is still in file systems

        by jcelko212 ·

        In reply to Simple but effective solution

        >>. Name things with nouns and actions with verbs. << You got that part right >> Names should be singular. A table holds many records [sic], but they are used one at a time. << Not in a RDBMS! SQL is a set-oriented language and rows are nothing like records. This was an old Military Standard that is being replaced. Since a table (which is nothing like a file) models a set, the data element name needs to be a collective noun (Personnel , not Employee) or a plural if there is no collective name. >> If a table name describes one record [sic], references to columns [sic: records have fields, not columns] will read better (i.e. agent.name). << You are mixing RDBMS and files together and have not read ISO-1179 yet. >> Records [sic] are acted on one at a time, so procedure names should be for a singular action. Plurality does not add anything and interfere with code readability. << Actually, collective and plural names are very useful in SQL. I can have a table and column with the same name in SQL -- bad programming, but legal. The collective noun lets me think in terms of sets and not single entity processing, like I would in a file. >> Prefixes or suffixes can be used to group objects without making names extremely long (i.e. “f” for finance or “s” for sales). << Why are you worried about length? SQL allows 128 characters in a name in the major products. Short abbreviations get confused easily -- is "P_" for "Personnel" or for "Products"? >> Sometimes, subgroups are useful (i.e. “ar” for accounting receivables and “ap” for accounting payables). Using and underscore (“_”) after a prefix or before a suffix makes them stand apart from the name. Thus, the prefix or suffix does not need to be a fixed length to define it. << Underscores do work and you can measure the eye movements involved. camelCase and Hungarian Notation do not work. Everything else that is put into the name identifies the type of object or restricted use. >> The context will usually identify the type of object to a programmer. Identifying the object in the object name usually states the obvious. For example, “select name, street, city, state, postal_code from customer, address where fk_customer =
        customer.pk_id”. << The goal is to have data element names be context-free!! That is the reason for a Data Dictionary! You are still writing COBOL file descriptions. Even worse; " fk_customer = Cutomers.pk_id " gives the same data element TWO DIFFERENT NAMES!! Then the prefixes (ISO-11179 uses postfixes, by the way) tell us HOW something is used locally and not what it logically is. Also, think about what a "pk_id" would mean; an identifier has to identify a particular kind of entity. There is no such thing as a magical, universal "id" floating by itself in a valid data model. >> Putting “col” in name, street, city, state, or postal_code adds nothing. << Sorry, wrong again. These are attributes and they need to be particular attributes in the data dictionary. Is it "state_code" , state_nbr" or what? >> Likewise, putting “table”, “ta”, “tb”, or some other code in name or address provides nothing. The context identifies the columns and the tables. << They are stupid for another reason -- they are the implementation method and have nothing to do with the logical data model. When I see "vw-" prefixes, I think of "Volkswagens"; >> The prefixes, “pk” for primary key and “fk” for foreign key, identify the columns according to use. This kind of key identification should only be used if the columns are constrained as keys. In this case, there may be multiple addresses for each customer. The primary key is unique. The foreign key is not unique but must exist in the parent table. Composite keys could be identified with a prefix of “ck”. << Absolutely wrong and a horrible way to screw up your data dictionary! Do you change your name (or DNA) every time you walk into a new room? You are telling me what a data element does in one table; usage is not identification. I want to know what it is in the logical data model and want it to have one and only one clear name. >> Packages, procedures, triggers, and sequences should be documented so that a programmer will know how to use them without reading the code. Therefore the name does not need to identify the type of object. A package is a group of objects and should be named with a noun. Procedures and triggers take action, so they should be named with a verb. << Now we are back to classic Software Engineering for procedural code. What you described is the old COBOL file system approach to ADP, not how we do it with RDBMS and data modeling.

      • #2525650

        Some good points here…

        by mattohare ·

        In reply to Simple but effective solution

        I like the idea of the business tag at the start. I do that with my views, stored procedures, and user-defined functions. I’m going to start doing it with my tables, I think.

        You also brought up the point of tags at the end. Tags that talk of the role of a field in a relationship. I used to use ‘ItemID’ for the primary key name, and ‘Item’ for the foreign key side. The idea was that the foreign key field represented the whole object. My boss at the time said it was quite confusing if there was just an ID number there, so we decided on the “FK” tag at the end.

        I must agree with the other lad about using plurals, even if I don’t fancy his delivery style. When I make a list for a trip to a bookseller, I call it a list of books. Not a list with a book and a book and a book and a book…. That’s the context in my head when I name the tables in the plural.

    • #2538651

      I already wrote a book!

      by jcelko212 ·

      In reply to Database Naming Standards

      You have not seen a copy of my SQL PROGRAMMING STYLE!

      It is based on the ISO-111769 Metadata standards and a few decades of research. Before I was “The SQL Guru”, I did Software Engineeering research for the US Army. Readability and maintaining code was a huge deal back. Good naming conventions and formatting can save 8-12% of the programmer’s time when maintaining code.

      • #2539216

        Book Titles

        by charles.cameron ·

        In reply to I already wrote a book!

        Should have been a book on arrogance.

        • #2537926

          Yeah – Just read

          by toms45 ·

          In reply to Book Titles

          his online profile. Nuttin’ like blowing your own horn. Says he “travels well”. So does my old truck! Oh yeah! I just noticed I have twice as many thumbs up as he!!!

      • #2595755

        I just bought your book

        by ibmpro99 ·

        In reply to I already wrote a book!

        I just bought your book on Amazon for $18 plus postage.

        I believe that you have underestimated the impact of good naming conventions. My personal estimate is closer to 30% negative impact when no naming conventions are used and as much as 50% when conflicting and intentionally misleading names are used.

        What the hell is the “no-no-switch”?

        Answer – it’s the switch used to force shipment out of an alternate wherehouse when the normal warehouse is on a union labor strike.

        I found this is in an old Uniroyal COBOL program in 1980. All of the code in 100 of these programs was just like this!

        Did we have some fun that year converting this system!

        • #2595268

          Typical Problem

          by mattohare ·

          In reply to I just bought your book

          There’s a need to solve a problem, such as object names. Then people have to agree on a standard. You get different groups that want their needs met, others that want to do heck-all towards a standard. So we end up with too much of a system that many don’t use, or use improperly.

          I like to take it handy with the standards. Set a few tags to meet the basic needs and have others be free within that.

          Main thing I wanted from starting this thread is to learn what others use for tags. Some tag for business process, others for object type. I’m moving towards a hybrid of the two.

        • #2595258

          LOL!

          by jcelko212 ·

          In reply to I just bought your book

          I worked with a COBOL program the State of Georgia where the guy would pick a theme for his paragraph names. One of them used the names of countries (“GO TO FRANCE”), another was the names of flowers in his garden, etc.

    • #2525651

      Sidebar: Stored Procedures with “sp_”

      by mattohare ·

      In reply to Database Naming Standards

      I learned today about something in MS SQL Server, and I think Sybase. The database engine first looks in the master database, then the local database for procedures starting with “sp_”. The letters mean, in the engine’s context “system procedure” not “stored procedure” as several people seem to think. If, for any reason there is a procedure by that name in the master database, it will run instead of the database version. Unless of course, you’ve fully qualified the name to the local procedure.

Viewing 3 reply threads