Naming Standards, you need them. Consider them a rule book of Database Design. While we would all like to play without rules, rules in our life, sport and business ensure a level of consistency, and enable a higher level of interaction, input and support from others when we all know the rules.
In summary, Naming Standards will:
Which you use can be determined by a number of factors. Historically, Designers and Developers from an Oracle background will use and understand UPPERCASE. MySQL by default usually defaults to a lower_case standard. One reason for this is the limitation under Windows Based Operating Systems.
Personally, I used MixedCase within MySQL. I can discard the Windows Limitiation as I don't develop software to operate under with MySQL under Windows, I develop with, promote and only support Linux based MySQL installations. I also come from an Oracle background, and why use underscore '_' to distinguish information within object names when MixedCase can achieve this.
If there were only a few key debates in database naming standards, this is one of the first. As the heading suggests, should the name of your database tables be defined as singluar names or plural names.
For Example:
I've worked in large projects using both models. For lack of a better reason, I have used for most of my profession Singular table names as they are simplier. There are no rules to worry about for plural names, and as specified later when grouping tables, additional complexity is avoided.
Unless you are in a large and complex enterpise solution (and let's define this as 300+ tables), there is no reason why you should need to use short table names.
Be Descriptive, why use Cust when you can use Customer, CustOrd for CustomerOrder or CustOL for CustomerOrderLine.
Just these examples alone, introduces another key variable, relevence. Why does CustomerOrder not simply be Order. We should always be striving for simplicity. In this case there are two good reasons why to use CustomerOrder. The first is Grouping, the second is Reserved Words.
The first is a sense of grouping. By using the name CustomerOrder you are relating this table directly to it's required information. Indeed you can't have an Order without a Customer. The added benefit of this grouping, is like groups of table information are more closely together in an alphabetical listing of table names, and normally the core or key table for a grouping will appear first, as all related group tables will consist of this core name.
In the case of Order is the second reason. The use of Reserved Words. Indeed within MySQL unlike other RDBMS products, reserved words can be used in a quoted syntax, the default being the backquote (`), however double quote (") can be used in an alternative sql_mode.
For compatibility, reserved words should be avoided.
On that note, why should you make your database standard compatible with other RDBMS products when in theory your system many never be used with another RDBMS. If for example you are developing a product, you would most likely then be forced to use some layer of database abstraction, which greatly restricts you, including in the area of Reserved Words, and even in functionality.
MySQL is the open source database product of choice, but you may not always be using MySQL, indeed my career has spanned Ingres, Oracle, Sybase, MS Access, MS SQL Server and MySQL. In order to ensure less inconvience over your career it's a good principle to generally avoid known reserved words.
In review for the appropiate Naming Standards for Table.
Every table must have a primary key. This is not a requirement of a RDBMS table, but sometimes I wonder why not. The principle of a Relational Database and indeed a table is to group like information together (tables/columns) and to be able to uniquely each record of information (row of a table). There is really no place for any debate, and again for simplicity in a standard, all tables will have a primary key.
There are two options for Primary Keys:
I always use a Suggorate Key as the Primary Key for a table. The reasons.
Again, simplicity. If the rule is every table has a suggorate key, and it's called id for example, there is no confusion. If the rule for example, allows for intersection tables to have an optional primary key, this is reason for difference and different modellers in the same project could inteperate this differently, so stamp it out.
Note:There may be performance considerations in a larger enterprise solution which may govern the need for this not to enforced. I will be discussing this later in the section on Performance Considerations.
A natural key, while this may be easy for a Customer or an Order, it could be much more complex for other tables, and my involve multiple columns to uniquely identify a row, and this can become unweilding, especially if it's necessary in a child table to carry three columns from the parent table to act as the foreign key for a table. And ultimately, there will be an occurance when a suggorate key is required, so using a natural key approach will just result in the standard being, use a natural key when appropiate.
Using a Suggorate key will also ensure a level of performance improvement. For example, if we had a table of Customer in an online webstore we could enforce an email column as a natural primary key, as no two customers have the same email. However this column would a variable length, say VARCHAR(50), while a suggorate key, of INT is much smaller in relative disk size. This performance improvement is compounded when the INT primary key is used as a foreign key in other tables, and indexes on these foreign keys. Performance improvements will be made, and less disk space used.
For simplicity, does every table have a suggorate primary key of the same name, for example id, or is each table primary key uniqure across all tables. To discuss this and determine a best course of action, you need to also consider the name of foreign keys.
To name all primary key columns the same, you would choose something precise, short and representing the information being stored. id is a prime candidate. It's short and simple. You could try key, that represents the column, but this is a reserved word, and as with Table Names, reserved names while allowed in MySQL should be avoided. The primary key can be anything, as long as it's standard. Other examples I have seen include SGN (meaning System Generated Number), num and pKey for example.
⇑ TOPWhen using a column as a foreign key, it must be named differently using this approach. For example, if we had a table of Customer with a primary key of id, in the CustomerOrder table, the primary key for the order would be id, so we can't use id as the foreign key for the Customer table. This column would have to be named something like customerId or customer_id.
In this case, one perferred perference is to name the primary key the same as it's name when used generally as a foreign key, hence every table will have a uniqure primary key name. This is my preference.
There will always be exceptions, and with foreign keys, an exception will be necessary. When multiple foreign keys reference the same primary key, this standard cannot be used. There are two examples, the first is when the foreign key is indeed a key to it's own table, in this case, the prefix parent is generally accepted, and if used across the database model acts as an additional standard. When two columns which are foreign keys to the same table, it is best to prefix the column with it's purpose, for example createUserId and updateUserId may respresent two columns in the Customer referencing a table called SystemUser (Note: again, while the table could have been User, we are foiled by the Reserved Words constraint, and grouping all under a category of System is self explaining.)
Other considerations may be the programming environment or framework in use. Indeed home grown generated environments like standards, and having id ensures wiring of screens, handling of update and deletes is simplified. Ruby On Rails for example uses the standard of a primary key called id.
⇑ TOPNow to general column naming, by default columns are strongly typed, where the data type of the column is known, e.g. Date, Char, Interger. In more modern programming languages, variables are weakly typed, i.e. the data type of the column is not know at definition, and can change during programming operation.
As such, this strong typing can be reflected in the naming standard for columns. Here are a few examples:
There are several reasons why this can be benefical. Firstly, when looking at the schema of the database, for example in a phyiscal model diagram, a lot can be drawn from any SQL statement, if a standard naming convention is employed.
Second, programatically validation can be performed without knowing the underlying meta data. For example, columns ending in Date can be automatically parsed and validated to ensure they contain a valid date.
Third, with the implementation of a templating system, simple code generation can greatly assist in rapid development. Again using the Date Example, the following HTML code for an input field could be easily generated simply from the column name alone.
Microsoft for example, recommended standards for all variables in earlier programming standards, they were particularly strict for all data types. Indeed back in the days of Fortran 77, I learnt the data type was implied by the first character of the variable. Integers were named from i to n. Indeed, in most programming languages today, examples of a for loop starts with variables, i, j, k, not a, b, c. This being based from the Fortran Language.
Depending on your purpose, my rule is, if the content has some implied validation, then it a strong candidate for a column standard.
In most tables, I have the columns name and comment. You may use title, description etc. There is no benefit in being specific for example customerName or userName.
Using the simplicity model, you should use fully describtive variable names over application shortened abbreviations. Being able to read the name of the column and know immediately it's purpose and intended data type is far greater then requiring an additional resource. Unless the abbreviation is either generally recognisable, for example URL, or an industry specific abbreviation.
⇑ TOP
In early modelling, a more strict set of rules for column ordering was normal, generally following the rules:
A better general convention for column ordering is to use the appropiate above ordering, but it is generally accepted that like columns are associated together. Using the strict approach for a set of address columns they would be ordered throughout the table, and would result in a order of:
This is both impractical and unnecessarily complex, a more appropiate logical and better understood practice would be to group all columns (foreign key, mandatory and optional) in a business sense.
BEFORE ROW INSERT - BRIAFTER STATEMENT UPDATE- ASU
© Copyright 2006.
ARABX Pty Ltd.