Page 22 - Database-En-V3

The following must be noted when specifying columns in tables:

✓ It should not include calculated data - In most cases, the result of calculations
should not be stored in tables.

✓ Store data in smaller facts: If you combine more than one type of data in a
field, it will be difficult to retrieve individual facts later. Try to divide the data
into multi logical parts; For example, create separate fields for first and last
name or for address, zip code, and city. When you have refined the data
columns in each table, you are ready to choose the primary key for each table.

5. Define primary keys

Choose the primary key for each table. A primary key is a column that is used to
uniquely identify each row and whose value is non-recurring, such as a student code.
The rules for choosing or creating a primary key are as follows:

✓ Duplicated values in the primary key are not allowed. For example, do not use
people's names as the primary key; because names are not unique. You can
easily have two people with the same name at the same table.

✓ The primary key must have a value. If a column's value is sometimes
unassigned or unknown (missing value), it cannot be used as a component of
the primary key.

✓ You should always choose a primary key whose value will not change. The
primary key of a table can be used as a reference in other tables in a database
that use more than one table. If the primary key is changed, the change must
also be applied wherever the key is referenced. Using a primary key that will
not change makes it less likely that it will not be out of synchronizing with other
tables that reference it.

✓ A random unique number is often used as the primary key. For example, you
might assign a unique order number to each order. The purpose of the student
number is to identify the student later. Once set, it cannot be changed.

✓ In some cases, you may need to use two or more fields together to provide the
primary key for a table. For example, a Student Score table that stores course
grades could use two columns in its primary key: Student ID, Course ID, and
Seat Number. When a primary key uses more than one column, it is also called
a composite key.

15 Training unit in the field of technological information - at the Supreme Council of Universities © Intellectual property rights 2024