Relational Database Table Design Standards and Principles

TABLE NAMING STANDARDS

· The table name must clearly identify the one type of data the table contains.
 
   Table names should usually be plural.

· Table names are the only database objects not prefixed.

· Only the first latter of each word in a table name should be capitalized.

· Functional table names (for many-to-many relationships) should be a combination of the two tables referenced. For example, SubprojectsDocuments indexes the many-to- many relationship between Subprojects and Documents.

· Lookup tables should be grouped together. They can be grouped at the end by starting the name with the letter “z”. Lookup table names may be singular.

· Developer’s tables begin with “zz”. (Example: zzChangeLog)

FIELD STANDARDS

· Field names should be descriptive and clearly understandable by someone who looks at them for the first time.

· Field names should contain letter, number, and underscore characters (“_”) only.

· The primary key field should always end with “ID”. If the field is not autonumber, all relationships to that field must have referential intergrity with cascade update.

· A field referencing a primary key field in another table should have exactly the same name as the field it is referencing.

· If multiple fields in a table reference the same primary key in another table, the field names should be followed by a suffix. (Example: BankID_from BankID_to)

· Abbreviations for field names should be avoided. An exception is when the table name contains or more words, the initials of each word may be used, for any field except the Primary Key field, and the description field. For example, if the table name is “FacilitatingPartners”, a field may be named “FPName”. The Primary Key would be “FaciliatingPartnerID”.

· Fields with Dari text have a name ending with “_dari”

· Composite keys should never be used. If necessary, use a composite index instead.

· Field names should be unique throughout the database.

NORMALIZATION PRINCIPLES

· A field is a fact about a particular subject (the field contain one piece of data).

· Fields in a database should be fully functionally dependent on the primary key.

· All non-key fields should be mutually independent. For example, a calculated field should not be stored. (Note: sometimes this rule must be violated for performance reasons.)

JOIN PROPERTIES

· Inner Join – contains matching records from both tables

· Left Join – contains all the records from the left table, and matching from the right

· Right Join – contains all the records from the right table, and matching from the left

JOIN TYPES

· One-to-one

· One-to-many

· Many-to-many – requires an intermediate table