DB design standards

Database design standards are collections of coding rules, guidelines, and best practices.

General

Table, field names and all other user defined names should be stylized as upper camel case (initial uppercase letter, also known as Pascal case).
For table names use plural.
Primary key should be named "ID".
Foreign key in another table should be named by singular form of primary table name.
Composite keys should be avoided. Surrogate INT AUTO_INCREMENT keys can be used as a replacement.
Columns set to the smallest size possible.
Avoid Nullable columns. Nullable columns require extra space within the database to designate the fact that they are nullable.
Invoices
InvoicesItems

Lists

Naming and type conventions of required field names for lists. Use bellow default field sizes if not required other.
CREATE TABLE Customers (
  ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR (50) NOT NULL,
  ...
  Active ENUM ("Y","N") NOT NULL DEFAULT "Y",
  InsertUser SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  InsertDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ChangeUser SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  ChangeDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (Name))
  COMMENT = "Basic customers data";

Master-detail tables

Naming and type conventions of required field names for master-detail tables. Use bellow default field sizes if not required other.
For detail tables, detail table name should be composed first of masters table name then descriptive name of details (or use general name "Items").
Proper foreign keys must be created.
CREATE TABLE Invoices (
  ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ...
  Customer SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  ...
  InsertUser SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  InsertDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ChangeUser SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  ChangeDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (Customer) REFERENCES Customers (ID) ON UPDATE CASCADE)
  COMMENT = "Master data of issued invoices";

CREATE TABLE InvoicesItems (
  ...
  Invoice INT UNSIGNED NOT NULL,
  ...
  FOREIGN KEY (Invoice) REFERENCES Invoices (ID) ON DELETE CASCADE ON UPDATE CASCADE)
  COMMENT = "Detail data of issued invoice items";


 

© 2003-18 iNeta d.o.o. | Koroška cesta 31, SI-4000 Kranj | info@ineta.si | Pravno obvestilo
Powered by BravoCMS

Warning: Unknown: write failed: No space left on device (28) [Unknown:0]

Warning: Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/var/www/htdocs/ineta.si/tmp) [Unknown:0]