Normalization reduces redundancies and other problems that would undermine the integrity of your database.
The primary key which is a unique identifier which must always have a value which never changes and has a unique value for each record in a table. The other is the foreign key which is a primary key that is linked to another table.
One to one: One item in column A can relate to only one item in column B One to many: One item in column A can relate to many items in column B Many to many: Multiple items in column A can relate to multiple items in column B
By normalizing your database.
INDEX UNIQUE FULLTEXT PRIMARY KEY The type to columns that should be indexed are columns that are frequently used in the WHERE part of the query. Columns that are frequently used in a ORDER BY part of query and columns that are frequently used as the focal point of a JOIN query.
The two most common table types are MylSAM (which is for most application handling SELECT and INSERT very quickly but are vulnerable to corruption and data loss should a crash occur) and InnoDB (which per updates nicely, but is generally slower and requires more disk space). You default table type really depends on the version of MySQL you are using. Prior to version 5.5.5 MylSAM was the default table type but as of version 5.5.5 InnoDB as become the default type.
A character set is another term for encoding. Encoding is a series of numbers and letters which dictates what characters and languages are supported.
Collations are rules for comparing characters in a set and ties to the character set being used. Collation takes into consideration the language that is being used reflecting the type of characters being used as well as the cultural habits of those people. I’m using Charset utf8 and collation utf8_general_ci.
UTC stands for Coordinated Universal Time which is considered time zone neutral.
To convert from UTC to another time zone use CONVERT_TZ() using the arguments dt, from, . The first argument is the date and time, and send and third arguments are named time zones. In order to use this function, the list of time zones must already be stored in MySQL.
Foreign key constraints are a way of creating a relationship between two tables which in case of break between the tables occurring, the foreign key restraint will set rules as to what would happen if this break occurs or to prevent this break from happening all together.
InnoDB is the table type that supports foreign keys.