Choosing the columns in each table
The next step is to choose the columns in each table. The columns describe the properties of each entity in the table.
A good rule of thumb when choosing columns is to avoid duplicating data. For example, in the Arrow Aircraft application, you know shareholder names will have to be used in association with the itinerary data to answer the following Flight Ops question: "What are the shareholders catering needs for a particular itinerary?"
One approach is to have shareholder first and last name columns in both the shareholders table and the itineraries table. However, this would require not only duplicating the name data in two tables, but also several times in the itineraries table (you would have to enter the shareholders name in the itineraries table every time the shareholder requested a new itinerary). This approach increases data-entry effort, introduces opportunities for errors, and threatens the datas integrity (a change in one part of the database must be done in other parts).
A much better solution is to enter the shareholders first and last name a single time in a single table, the shareholders table. When the time comes to answer the Flight Ops question above, you can join the shareholders table with the itineraries table using a SQL expression.
Make sure each of your tables has a primary key column. A primary key column contains values that are unique for each row. This allows you to zero in on the exact row you want when searching the database. Most primary key columns consist of ID numbers, but you can use natural primary keys such as government form numbers or aircraft serial numbers.
After some thought, you choose the following properties and primary keys for the tables in the Arrow Aircraft database: