A foreign key is an attribute that relates, links, or ties, an instance in one entity to an instance in another entity. In other words, a foreign key is a copy of the primary key in another entity. In the employee table above, we could add another column that lists off each employee's position. It would look like this:
That single number represents each employee's position, which is related to the primary key on the position table. The position table includes all of the attributes of that position and has the information for each position stored in each instance. This helps with data integrity, because we don't have to list out all of the position attributes for each person. If we need to update the data, we only need to go to the position table and it will be changed for every person.
Foreign keys behave differently depending on the type of relationship between two entities. In a 1:M relationship (the most simple example), the foreign key will always go the table on the "M" or many side. With employees and positions, at maximum a position can have many employees, so the employee gets the foreign key.
In 1:1 relationships, it is considered ok to put the key on either table. There is a best practice that is ususally followed in industry, however. The minimum cardinalities can be used to determine the best placement. For example, consider a person entity and a passport entity. Unless you have dual citizenship, at maximum a person has one passport and at maximum a passport belongs to one person. Let us ignore dual citizenship and consider this cardinality to be true. Now, at minimum a passport has one person and at minimum a person has zero passport.
lets turn this into a table, and your name is the first instance on the person table. Go ahead and draw it out with attributes and a primary key. Add one more person to your table and give them attributes. Now, draw a passport table. Give the table attributes and a primary key (color, nation, etc.). Only make one passport on your passport table. Now lets try to add the foreign key in the relationship.
Add a column to the person table for the PassportID to be added as a foreign key. Put your one passportID under this new column in the instande that represents you. Now look at the table. There is an empty box Where your friend's passport number should be. They don't have a passport, so this results in wasted space in your database. A database must store that there is nothing in that box, which is inefficient.
Erase the column you added, and add a new column on the passport table for the PersonID to be added as a foreign key. Add your PersonID to the first instance in this column. Look, there isn't any wasted space! This is the best way to do 1:1 relationships: in a 1:1 relationship, add the foreign key to the one that has the zero in the cardinality. If your minimum cardinality is 1:1, it truly doesn't matter, but remember to only add a foreign key to one of the two tables.
In many to many relationships, we use a composite key. If you added the foreign key to just one or the other table or even both, you would be unable to correctly identify how two instances are related. Instead, we create a linking table that contains both keys and any other attributes that would be useful to know. One example of this is order and item.
An order can contain many items, and an item can be on many orders (think apples and cereal). The ERD looks like this:
This is a special relationship, because the table in the middle doesn't actually contain a primary key. The combination of the foreign keys creates a single primary key to accomplish the purpose of a primary key: to uniquely identify each instance. The table could also be used to record the quantity of each item purchased. This is the standard procedure for every M:M relationship. Be careful though, because some M:M relationships actually have another real table in between them. For example, trying to relate a customer and the items with a many to many makes sense, because a customer can buy many items and an item can be purchased by many customers. The middle table could represent this, but the order table is missing that records the date and other information from the sale, like which employee made the sale. A customer is really realated to order, which has the relationship explained above with item.