RelationalDBDesign 




Database Analysis   «Prev  Next»
Lesson 5 Table Column Rules
Objective List the rules for table columns.

Rules for Columns of Databases

When you create a table, you use the columns to store attributes of the entity represented by the table.
For example, a computer might have attributes of manufacturer, processor speed, amount of random access memory, hard-disk space, floppy-disk drive, speed of a CD-ROM drive, and monitor size.
Each of those attributes is represented as a column in the Computer table.
Computer table
Computer table

Each column in a table must adhere to two rules:
  1. The column must have a unique name within the table.
  2. The values for the column must come from a single domain. [1]
    (You will learn about column domains in a later lesson.)
The first rule seems obvious: every column must represent a different attribute of the entity represented by the table. If the same attribute occurs in more than one table, a relationship[2] exists between those two tables. Imagine that your database contains two tables: one listing computer manufacturers, the other listing computers owned by your company.


ManufID field creates the relationship between these tables. ManufID is the primary key for the MANUFACTURING table and the foreign key for the COMPUTER table.
ManufID field creates the relationship between these tables. ManufID is the primary key for the MANUFACTURING table and the foreign key for the COMPUTER table.

These two tables have the ManufId column in common.
Establishing relationships between tables enables you to link tables in a query.
If you have a problem with a computer and need to contact the manufacturer, you can look up the computer's record in the Computer table and, with the ManufId value in the record, find the manufacturer's contact information.
Another benefit to establishing relationships between tables is that, if two tables are in a
one-to-many relationship, you can ensure that every record in the table on the many side has a related record in the table on the one side.
In the graphic above, it would make no sense to enter a manufacturer into a record describing a computer unless that manufacturer was listed in the Manufacturers table.

If you enforce referential integrity between two tables, your Relational Database Management System (RDBMS) will prevent you from making that mistake.
one-to-many relationship: In relational database design, a one-to-many (1:N) relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity B; but for one instance of entity B, there exists zero or one instance of entity A.
  1. Referential integrity: The means of maintaining the integrity of data between one or more tables that relate to each other. In other words, a column of data in a table has a null or matching value in a corresponding table.
    Referential integrity is usually enforced with foreign keys.
  2. Relational Database Management System (RDBMS): A software package that manages and provides access to a database. These packages follow Codd’s 12 rules of relational databases and normally use SQL to access data.
The next lesson lists the rules for table rows.
[1] Domain: Determines the type of data values that are permitted for that attribute.
[2] relationship: If the same attribute occurs in more than one table, a relationship exists between those two tables.