Schema/Table/Column/Row

Schema

Also known as a database.

When schema is created, MySQL treats it as a sub-directory of it's data underlying filesystem.

The most important thing when create a schema is : character encoding.

• Character encoding : It depends on your specify system. For example, if your system need to contain wide character, japanese character,.. utf8 charset encoding is not enough, you may need utf8_mb4,...etc.

Table / Column / Row

Basically, when table created, MySQL will make a file to store table's definition (.frm file) and a data file.

Table is built up from columns(fields), it's content is rows.

Table always define a primary key. If there is no explicit define primary key for table MySQL has a mechanism to create an implicit primary key for table, but we cannot use that key.

The most important things when creating a table are : fields, primary key, constrains, storage engine, charset

• Storage engine : when you choose a storage engine for your table, it means you take all advantage and disadvantage of that engine. Because each storage engine has different advantage and disadvantage so choose the right one will save performance of whole your system. Example : if your table's storage engine is InnoDB, so you can execute transaction but if you choose MyISAM for your table, you cannot execute transaction.
• Fields: they are skeleton of table. It decides how wide of the table is. Field is defined by content type, content length, charset, default value and some other options.
• Rows: they are data of table. It decides how large of the table is.
• Primary key: when listing up fields of table, you will consider one of these fields will be the primary key - it's value will indicate the row distinct from other row. It's very important to process data in table because almost query will lookup data via primary key to execute.

Note from writer:

• You should consider about the content of database and choose right charset, right storage engine before do anything. It means you have to think about what your table contains? What you will do with data in your table? Is it read only or read/write frequently? Choose carefully charset and storage engine for database and table will save your system performance and cost.
• Always normalize your tables with : 1NF, 2NF, and 3NF. NEVER FORGET. It make your database more consistence.
• Always have primary key for your table even if it's unnecessary, redundant because primary key is the most index key of your table data. So access data with primary key will save performance of system.
• Always set the max length of content type for each field, explicit default value.
• Remember create key index for the column that you analyze that they will be use in most query condition.