Software

Ask something
Discussion Locked

Database Naming Standards

0 helpful
13 replies

by mattohare

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

13 replies

by mbeards

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.

by jcelko212

>> 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.

by mattohare

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.

by c_tharp

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.

by jcelko212

>>. 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.

by mattohare

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.

by jcelko212

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.

by toms45

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!!!

by ibmpro99

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!

by mattohare

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.

by jcelko212

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.

by mattohare

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.

This discussion is closed to new replies.