Tables, Columns and Rows

We can’t get too far in talking about any database management systems without discussing the concept of a table. And the constituent parts of table called columns and rows. So let’s take a look at these, and give them some particle definitions. A table is the basic storage structure for data in any relational database management system, generally a table is used to contain the data for a specific entity in our application, such as a user or products. We will have many tables within a given database. A table cannot exists without being defined, we define a table by describing the kinds of data that table is to contain, that is what pieces of information do we want to store that describe a specific entity of the type for which the table is intended. We call these descriptor “columns”, so a column is a part of the definition of a table, a column exists even before we put any data into the table. Each table must contain at least one column. And a table with only one column isn’t very useful, so most tables will have many columns. A column has two basic characteristics, a name which must be unique within that table, and a data type which indicates whether that column stores numbers, character strings or date n time information or Boolean data. The content of a table is then added as “rows” of data. Each row contains the value of the appropriate data type for each column in table’s definition. The values in each row describe a specific entity that is the values in a row all belong together. We can think of a row as the data for an individual member of the table and the values contained in the row as properties of that member. In MySQL there is an additional twist to the way data is stored in a relational database management system, we must assign a storage engine to each table. The storage engine determines how the data of that table is to be handle like: what files must be set up and maintain storage for that data and other things. Each storage engines offers additional mechanisms and internal structures intended to streamline data access for specific circumstances.

Here’s the way we usually visualize a table.

mysql table structure

mysql table structure

Notice that that on top is column definitions and below data contents. Also each row has an id value in this case “car_id”. Depending on the storage engine used for the table there may very well be an internal id in addition to an id column we have defined in the table structure, it is always good practice to designate a column or small group columns as a unique identifier for a row of data. This identifier is known as the primary key. In the next blog we will discuss database design.