Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available
Have you ever placed an order with a company for the first time and then decided the next day to increase your order? You call the people at the order desk. Sometimes they ask you for your Customer Number. You tell them that you don't know your Customer Number. This happens all the time.....
So they ask you for some other personal information, generally your Postcode or telephone area code. Then, as they narrow down the list of customers, they will ask your name. Then, they will tell you your Customer Number. Some businesses use phone numbers as a unique starting point.
Database systems usually have more than one table, and these tend to be related in some manner. For example a Customer table and an Order table are related to each other via a unique Customer Number. The Customer table will always have one record for each Customer, and the Order table has one record for each Order that the Customer has made.
As each Customer in one physical person, you only need one record for the Customer in the Customer table. Each Customer can make several Orders, however, which means that you set up a table to hold information about each order (the Orders table). Each individual Order has one record in the Orders table.
Of course, you relate the Customers' Orders in the Orders table to the correct Customer in the Customer table by using a common field between both tables. In this example case, we would use the Customer Number (which is included in both tables).
When linking tables, we link the primary key field from one table (the Customer Number in the Customers table) to a field in the second (related) table that has the same structure and type of data in it (the Customer Number in the Orders table).
If the link in the second table is not the primary key field (and usually it isn't), it is known as the foreign key field.
Besides being a common link field between tables, a primary key field in Microsoft Access has the following advantages:
Primary key fields should be made as short as possible as this can affect the speed of operations in the database.