Here is another sample naming standard:This naming standard uses shorter object names appropriate for a small database on a PC computer. We are using the template SSSEEEOM where:

SSS identifies the application system. Here CUS is for CUSTOMER.

EEE shows the entity within the application such as MST for MASTER, ORD for ORDERS, etc.

O reflects the type of object, such as:
D = Database
S = Schema
T = Table
V = View
X = Index
C = Constraint

M is for multiple occurences.

The CUStomer ORDers Table is CUSORDT.

The CUStomer ORDers inDeXes are CUSORDX1 and CUSORDX2.

Here is a sample naming standard:This naming standard uses the long object names of the SQL standard.

Prefixes:
D_ = Database
C_ = Catalog
S_ = Schema
No prefixes on lower higherarchy objects.

Suffixes:
_TBL = Table
_VIEW = View
_IDX1 = Indexes
_ID = Column definition of a key
_PK = Primary key
_FK1 = Foreign keys
_CK1 = Constraints
_UNQ = UNIQUE
_CHK = CHECK
…

All object names must be unique or implicitly or explicitly qualified by the next higher object and can not be SQL reserved words.

The vendors’ specifications vary from the rules specified by the SQL2003 standard.

 

Object naming standards fall into two categories: 1. naming conventions and 2. identifier rules.SQL standards say little about naming conventions, but certain conventions have been adopted by programmers with many years of experience:

Choose names that are descriptive and meaningful with underscores. FB127 is meaningless. YEAR_TO_DATE_SALES or YTD_SALES is easily understood by everyone. Others may be using your tables and the meaning is clear to everyone. Each vendor has naming limits, but their size is generally large enough to be meaningful. Names like YEARTODATESALES are harder to read than YEAR_TO_DATE_SALES.

Use abbreviations consistently. Do not use YTD in some places and YEAR_TO_DATE in other places.

Use the same case consistently. Use all upper case or all lower case names. Postgre SQL converts unquoted object names to lower case, DB2 requires all upper case names, and other vendors are case sensitive. Using mixed-case names can cause problems.

Do not use double quoted names. Names bracketed by the double quote character (” “) are case sensitive and can be difficult to use.

Do not use manufacturer and brand names. Company and product names change.

A more structured 3NF is called the Boyce-Codd Normal Form (BCNF). A table is in BCNF if every determinate column is a candidate key. A determinate column is one on which some of the columns are fully functional. These are tables that have composite candidate keys, multiple candidate keys, or candidate keys that overlap.A table in fourth normal form (4NF) is in BCNF and has no multi-valued dependencies. A multi-valued dependency (MVD) is when one column has values in multiple rows that match a single value in another column of the same table. Suppose an employee works at multiple stores and is a customer representative for multiple customers. If you place all of this data into a single table, you must use all three attributes as the key because only all three attributes as keys can identify a unique row. The MVD relationship between employee and store says where each employee works. The MVD relationship between employee and customer says how many customers buy products from this employee. To change a table with MVDs to 4NF, move each MVD pair to a new table.

A table in fifth normal form (5NF) is in 4NF and has no join dependencies. 5NF table’s objectives are to have tables that can not be split into smaller tables. Most tables are already in 5NF and no further effort is necessary. Most people only have to normalize to 1NF, 2NF, 3NF, and sometimes 4NF.

A table in third normal form (3NF) must be in second normal form and have no transitive dependencies.
A transitive dependency is when a non-key column’s value determines another non-key column’s value.
A table is in third normal form when non-key columns are mutually independent and dependent on only
primary key column(s).

A domain-key normal form (DKNF) is a constraint of the domain (or range of permitted values) and the keys.
The sale determines the customer. Customer depends on sale and sale is a key.
The sale determines the item. Item depends on sale and sale is a key.
The item determines the price. Price depends on item and item is a key.
One table uses sale as a key and the other table uses item as a key.

Before we get into second normal form (2NF), note that if the primary key is a single column, or all the columns in the table are part of the primary key, then the table is already in second normal form.

A table in second normal form must be in first normal form and have no partial functional dependencies.

A partial functional dependency is when some, but not all of a composite key’s values determine a non-key column’s value.

To avoid this, a second normal form table is fully functionally dependent. That means that all non-key attributes (or columns) must depend on all components of the key. If the table has some non-key attributes that do not depend on all components of the key, break the table up into two or more tables so that, in each table, all non-key attributes depend on all components of the primary key.

To have a table in first normal form (1NF):

> The table must be two-dimensional with rows and columns describing a table object.

> Each row must have data that relates to the table object and no two rows can be identical.
Each table must have a primary key that uniquely identifies every row.
The primary key can consist of one column (simple key) or multiple columns (composite key).

> Each column must have a unique column name and contain data that describes an attribute of the table object and all of the column values must describe only that single attribute.
Each column must be consistent with a single data type. If this column is for a numeric phone number, then all entries in this column can only be numeric phone numbers.

> Each row and column value must be only a single value.

The process of “normalizing” tables into first, second, and third normal form (1NF, 2NF, 3NF) insures that your database will be simple, logical, and not have redundacies or inconsistencies.

First, convert your tables into first normal form (1NF). The normal forms are nested.

Converting to second normal form (2NF) can only be done after doing first normal form (1NF).

Converting to third normal form (3NF) can only be done after doing second normal form (2NF).

There are fourth and fifth normal forms (4NF, 5NF) as well as a domain-key normal form (DKNF) that give a higher degree of protection against modification anomolies.

Normalization is a repetitive process that involves repeatedly splitting and rejoining tables until the database designer is happy with the result.

When designing SQL tables, you go through several logical steps:> Identify all of your tables.

> Decide what columns are to be in each table.

> Decide what column(s) will be used as the primary key so that every row is uniquely identified.
Primary key will be discussed in more detail.

> See that every table has at least one column in common with other columns in other tables in the database.
These common columns provide the logical links to the data in the other tables making this a relational database.

> Now normalize these tables to eliminate irregularities and anomalies.