Principles of table design¶

Before you start creating database tables, you should carefully consider what data you want to include in them, and how the relationships between them should look like. It is also worth considering general rules such as:

• purpose definition
• avoiding duplication of data
• preservation of the atomicity of information
• avoiding empty fields
• unambiguous identification of records.

Goal definition¶

The structure of the designed database should be selected according to the specifics of a particular system. The modeled system will be different, in which personal data are its important point (preparation of separate tables for persons and addresses along with the relationship), and the system in which personal data performs a secondary function and is not an important point of the system (a common table connecting data personal and addresses).

Avoiding duplicate data¶

It is absolutely necessary to avoid duplications of the same data in the following lines.

For the above example, some information, such as the manufacturer's name and the manufacturer's address, are repeated many times in the database. Firstly, this is a waste of memory and, in addition, any mistake will result in inconsistent data.

In this case, it is much more effective to break the given table into two and enter a specific type of relationship, e.g .:

In the example above, the one-to-many relationship was used.

Atomicity of information¶

Each database field should contain individual (ie atomic) information. Atomicity is system dependent, but in short, it is about minimizing the amount of information stored in a given field.

For the example above, the manufacturer's address contains a large amount of information and, for example, searching for a specific product by address can be very difficult. In this case, depending on the actual needs, it is possible to introduce an additional table and bind it relation with the publishing table.

Another important aspect is the multiplication of references to another table. For example, having a situation where a customer rents a lot of computer equipment, he can implement these assumptions using the table below:

In the above case, 75% of the records have empty the Remarks field. For the above case it is definitely better to introduce an additional table that will be responsible for storing additional information and reducing empty fields.

Avoidance of empty fields¶

In tables, you should avoid leaving empty fields (with no data). In certain specific situations, however, null fields may be unavoidable.

Unique record identifiers¶

Each record in the table should be uniquely identified. Otherwise, it will not be possible to distinguish them. The master key must be well identified. Sometimes its abstract form can be replaced with a real identifier, eg ISBN for books, PESEL for Polish citizens. It all depends primarily on the context of the modeled system.