Actions

Archived

Database schema standards

From Joomla! Documentation

Revision as of 20:42, 30 September 2012 by Uob opensource (Talk | contribs)

Replacement filing cabinet.png
This page has been archived - Please Do Not Edit or Create Pages placed in this namespace. The pages in the Archived namespace exist only as a historical reference, it will not be improved and its content may be incomplete.

Contents

Naming Conventions

General Naming Conventions

There are many conventions used when naming tables or fields, so others are capable of understanding the contents of the table. While there are no absolute conventions, the following are used quite often, with the intention of clearer SQL statements.

  • No spaces. Using spaces is highly not advised when naming tables or fields. It makes SQL statements more complicated and harder to understand. In order to identify a gap in between words, underscores could be used. However, the preferred method is the use of Pascal casing.
  • Pascal casing. This practice is known by many other names, but will be referred to here as Pascal casing. This involves using a capital letter at the beginning of each new word.
      Examples:  ExampleTable, TableOfContents
  • Singular names. The use of singular names is just a simpler way of writing names. While plural names could be used, singular names are shorter and easier to understand.
      Example: Student (as opposed the Students)
  • Simple, yet describes the contents of the table or field. Names should be as small as possible, while still giving a user a clear indication of its contents.
  • Avoid similar names. When making larger databases, and having many tables, the tables should be clearly identifiable. If 2 or more tables have similar names, consider renaming one of the tables or combining the contents of them both. The same could be said for fields, to a lesser extent.

Table Naming Conventions

Using prefixes. Helpful in distinguishing the different types of tables. While the prefix should be in lowercase, use Pascal casing to begin the next word. There are other types of tables; these are just the more common types. Similar types of naming conventions could be used for the types of tables that are not listed here.

  • Data tables.Standard data tables should have the prefix: tbl
      Example: tblExample
  • Link tables. Tables that link 2 or more data table with a many-to-many relationship should have the prefix: link
      Example: linkVendorProduct

Field Naming Conventions

  • Using a prefix.

Fields in tables. Prefix should consist of the first 3 letters from table’s name. This is to easily identify which field belongs to which table. If any prefixes conflict, then use additional letters for the prefix. For example:

      Table name: tblExample
      Field name: exaField
  • Link tables. Due to the nature of link tables, the above prefix doesn’t make much sense and is redundant when used in link tables. Instead the same prefix link is used, much like the link tables.
      Table name: linkVendorProduct
      Field name: linkVenID
  • Primary keys. The general name of a table’s primary key should be the first 3 letters of the table name followed by ‘ID’. Similar to the general prefixes for other fields. Depending on the contents of the table, this may vary, however all primary keys should be unique of one another.
      Table name: Customer
      Primary key: cusID
  • Foreign keys. Use the standard prefix of 3 letters, but follow it with the referred table’s full name, followed by ‘ID’.
      Table name: Product
      Reference table: Customer
      Foreign key: proCustomerID

Data types

There are a number of data types used in databases. The following are the common ones used.

  • Integer (int): Integer is a data type that stores whole numbers.
Storage (Bytes) Minimum Value Maximum Value
4 -2,147,483,648 2,147,483,647
  • Float: A float data type is one that holds fractional values. That is they contain a decimal point that can “float” to the left or right depending on exponent. Floats have single precision. When defining a float, the total number of digits and the number of digits after the decimal point is declared.
Storage (Bytes) Minimum Value Maximum Value
4 +1.175494351E-38 +3.402823466E+38
  • Double: Double is a large floating point number. The double data type has double precision and like floats can hold fractional values.
Storage (Bytes) Minimum Value Maximum Value
8 +1.7976931348623157E+308 +2.2250738585072014E-308
  • Bit: Bit is a data type that is used to store bit field values. Bit(M) will store M bit values, where M ranges from 1 to 64. Assigning a value to the bit field data type that is less than M bits long will result in the value being padded on the left with zeros.
  • Date: This data type stores the date but no time. The format of the date is ‘YYYY-MM-DD’
Storage (Bytes) Minimum Value Maximum Value
3 1000-01-01 9999-12-31
  • Datetime: The datetime data type stores both date and time. The format of datetime is ‘YYYY-MM-DD HH:MM:SS’.
Storage (Bytes) Minimum Value Maximum Value
8 1000-01-01 00:00:00 9999-12-31 23:59:59
  • Time: The time data type stores the time value. The format of time is ‘HH:MM:SS’. The hours of the time data type is large because it can be used to display the time of day, elapsed time or the time interval between two events.
Storage (Bytes) Minimum Value Maximum Value
3 -838:59:59 838:59:59
  • Year: The year data type is used to store the year values. By declaring the year as Year (4) a four digit year is specified. Declaring the year as Year (2) a two digit year is specified.
Storage (Bytes) Minimum Value Maximum Value
1 1901 2155
  • Char: The char data type holds strings or text. This data type is a fixed length data type and is declared with the length. The length specifies the maximum number of characters that are to be stored. If the text stored is less than the length then the remaining length is padded with spaces. The total number of storage in bytes is dependent on the length of the characters.
Minimum Length Maximum Length
1901 2155
Quill icon.png
Page Actively Being Edited!

This archived page is actively undergoing a major edit for a short while.
As a courtesy, please do not edit this page while this message is displayed. The user who added this notice will be listed in the page history. This message is intended to help reduce edit conflicts; please remove it between editing sessions to allow others to edit the page. If this page has not been edited for several hours, please remove this template, or replace it with {{underconstruction}} or {{incomplete}}.