Some advice from our oracle consultant on database names: -Margaret Votava

Some advice from our Oracle consultant on database names: -Margaret Votava

Make names meaningful. Avoid names like POD for po_details. After all, what's a POD? Most databases have reasonable length limits to their field name sizes. Oracle's limit is 30 characters. So names need not be squeezed to fit silly limits like the old DOS 8.3 filename limit. Reasonable abbreviations are of course acceptable. A purchasing application might often use the acronym PO, and there is no confusion as to what it means. Other abbreviations, like no or num (for number), or abbreviations that are understood for you application are ok too. Just be consistent with your use of abbreviations vs your use of full spelled out names. Typically, abbreviations are often avoided in table names unless there is another part to the name. For example, you won't often see a table named POS. Instead you'll see PURCHASE_ORDERS, but you then may see PO_DETAILS. While this may seem inconsistent, the POS table might be confused with other terms - maybe point of sale, price of sale, physics online streaming, prince of spain, etc. You might want to argue that longer names are harder to type....I'll send you a file naming standard that I found on a quick search on Lycos. It appears to try to fit things in the old 8.3 limit. If you use the types on conventions in your databases then I guarantee database administrators, developers, and users will want your head! Databases should NOT require a database lookup just to be able to lookup something in the database. I shouldn't have to search find out that HREHP is the Human Resources table containing employee home phone numbers.

Use underscore (_) to separate parts of names - NOT spaces. Most databases allow the use of both, but if you use spaces you will have to do things like include field names in quotes when writing queries through any ad-hoc tools. e.g. select "my field" from "my table" where "my id field" = 'ABC' vs select my_field from my_table where my_id_field = 'ABC' The double quotes are confusing and painful for any database administrators or even worse for casual users. And, since databases tend to store their data dictionary information in fixed case (UPPER for Oracle), C++ style names of budgetCodes will look like BUDGETCODES when using dictionary views. The more readable BUDGET_CODES is preferred. Yes, underscore is the common separator in database table and column names.

Avoid words that your database reserves for its internal language, or items that it might later decide to use as reserved words. Also, think about the possibility of porting your application to a different vendors database and try to avoid things that might later get you in trouble. /* Take a look a MISCOMP databases for a bad example here. We used column names like ID, NAME, DESCRIPTION, which might cause a great deal of trouble if we ever move to something other than Oracle. For example, Dennis informs me that Sybase uses DESCRIPTION as a reserved word. */

Name your fields in order of most descriptive parts first. For example, EMPLOYEE_NUMBER and HIRE_DATE are better than NUMBER_EMPLOYEE and DATE_HIRE.

Avoid tense in your column names, keeping everything in current tense. For example, HIRE_DATE, CLOSE_DATE, OPEN_DATE instead of HIRED_DATE, CLOSED_DATE, OPENED_DATE. Ok, so if you're designing a database for the LA Dodgers, then you might indeed want a field named OPENING_DAY. But usually tense will just confuse people and current tense is used.

Use tense in your table names where appropriate. For example CANCELLED_PURCHASE_REQUESTS might make more sense to a user than CANCEL_PURCHASE_REQUESTS. Is the latter already cancelled or waiting to be cancelled?

Make table names plural (after all they hold more than one object) and column names singular. For example: table names like customers, orders, budget_codes - but - field names like customer_id, order_number, etc

Use consistent column names across tables to ease the user's learning curve. If a field represents the same data, then name it the same as fields in another table. If your EMPLOYEES table has an EMPLOYEE_NUMBER field as its key, then when this is used as a foreign key to a related table, use the same name, e.g. WEEKLY_TIME_SHEETS.EMPLOYEE_NUMBER is a proper table.column name. /* Again, MISCOMP is not a good example here. Our original choice to use the ID, NAME, DESCRIPTION convention causes learning curve where it shouldn't be needed.