database design
SQL Update
Added new entries to the expanding SQL section. The new sections separate the Datatypes and Functions into the correct groups. Updates to come will include pages on how to script them within each DBMS through SQL.
String/Text Datatypes
String data types hold a series of readable characters. These strings store values in plain text and should not be used for critical data unless it is encrypted. However, string datatypes can hold as little as a single character or several gigabytes depending on the type that is used. DATA TYPE SYMPLE TYPE Database DESCRIPTION [...]
Numeric Datatypes
Numeric datatypes hold any integer or decimal numerical value. Some DBMS have specialized fields that are formatted however in most cases any formatting must be handled through a special command in SQL or in the recipient language. DATA TYPE SYMPLE TYPE Database DESCRIPTION TINYINT(N) NUMERIC SQL Server, MySQL -128 TO 127 NORMAL. 0 TO [...]
Enumerated Datatypes
Each DBMS handles enumeration differently. The following datatypes are only available on MySQL. The other systems allow for custom datatypes or require a separate table to handle enumeration. It is important to know that these are there and available on some systems. DATA TYPE SYMPLE TYPE Database DESCRIPTION ENUM(X,Y,Z,ETC.) ENUMERATED MySQL LET YOU ENTER [...]
Datetime Datatype
Datetime datatypes are used to store date or time information within the database. Most types are available on all DBMS however some have more precision then others. DATA TYPE SYMPLE TYPE Database DESCRIPTION DATE DATETIME SQL Server, MySQL, Oracle, PostgreSQL A DATE. FORMAT: YYYY-MM-DD NOTE: THE SUPPORTED RANGE IS FROM ’1000-01-01′ TO ’9999-12-31′ DATETIME [...]
Binary Datatypes
Binary Datatypes are used to store binary data such as documents, images, or encrypted data. The most common usage is to store files within the database. This has the benefit of keeping them in a dynamic location without needing to constantly update links or locations. DATA TYPE SYMPLE TYPE Database DESCRIPTION BLOB BINARY MySQL FOR [...]
Math Functions
Mathematical Functions are critical for any application. This is because they can manipulate the different values stored in the database prior to outputting them. The functions can also be used to preprocess data that can be then inserted into a table or query for later usage. Function Database Return Data Type Description ABS SQL Server, MySQL, [...]
Datetime Functions
Datetime Functions are used to either retrieve or manipulate the datetime data types. The different DBMS have functions specialized to handle date time stamp objects. Each function can be used to express the data type in a different way. Most of the functions extract information from the data type; however, some of them allow for [...]
Conversion Functions
Conversion Functions are used to change from one data type to another. They can also be used to extract valuable information from a string or stringified array. The following is a list of the most common functions. It is important to know that SQL Server has several useful functions that are not available in other [...]
