Modern relational database systems are used to enter, organize and report on all sorts of data. Before considering what sort of software you might use, you should understand the various ways that your data could potentially be organized. In 1978, Dr. Peter Pin-Shan Chen of MIT wrote a crucial paper about understanding data entities and the relationships between them. One of the key concepts of this "Entity-Relationship" model is use of a primary key.
A database is nothing more than a list of information. An individual item in a database is called an entity or record. Entities can refer to real-world objects and people, such as a product or a student. They can also refer to abstract concepts, including an event or a purchase. In a relational system, similar entities are grouped together in a collection called a table.
Entities and Relationships
In a database, some entities have relationships to other entities. There are three different major kinds of relationships in Dr. Chen's model: the one-to-one relationship, the one-to-many relationship and the many-to-many relationship. For example, a class might meet in one particular classroom out of a list of classrooms. However, that one class has many students. Each of those students in turn is enrolled in many different classes.
What connects each of these entity tables is the use of keys. Keys identify records to form relationships. A "primary" key in a table record uniquely identifies that record. When that key is used as a field value in a record in a different table, it's referred to as a "foreign key." A table of class registrations might contain the following fields, for instance: a record identifying number or ID, a student's ID number and a class ID number. The student ID and the class ID in such a case would be foreign keys used to tie that one registration record back to an individual student or class in their own separate table. Without keys, these relationships could not be maintained.
Primary Key Constraints
There are specific rules for designating an individual piece of information, or field, as a primary key. Primary keys must always contain a value. They cannot be empty. Primary keys must be unique within that entity's table. Primary key values must never change. All of these rules are for practical, common-sense reasons. For example, a table of students at a school might contain many different students named "John Smith." In this case, a name field would not be a good choice for a primary key because it would be impossible to uniquely distinguish which "John Smith" you meant. A better practice is to have a system-generated, unique ID number created at the time the record is entered.
Using the entity-relationship model, selecting primary keys, establishing foreign keys and laying out the overall structure of your database in a series of entity-relationship diagrams is certainly a challenging process. Doing this work ahead of time provides you with a logical blueprint to begin actually creating your database in the software package of your choice.
Databases built using the ER diagramming technique, and those that use primary keys to establish relationships, are much more likely to account for data accurately and efficiently. In the previous example of a student registration database, a small table with registration records is highly organized, uses less space than repeating any of the field elements from other tables and makes entry simpler for the end user by allowing them to choose selections from other tables by ID.
- Photo Credit blueprint image by Igor Zhorov from Fotolia.com
The Purpose of the Primary Key in Access
Microsoft Access uses primary keys to uniquely identify each record in a table. The database administrator defines a primary key when he...