Choosing where to attach a foreign key between two tables?

Question

I have a purchase order table and another table to contain the items within a particular purchase order for drugs.

Example:

PO_Table (POId, MainPharmacyID, SupplierID, PreparedBy)
PO_Items_Table (POItemID, ...)

I have two options of choosing which table to link to which and they seem both valid. i have done this a number of times and have done it either way.

• I would love to know if their are any rules to where to attach a foreign?
• In my situation where do i attach my foreign key?

Update:

My two options are putting POItemID in the PO_Table or putting POId in the PO_Items_Table.

Update 2:

Assuming the relationship between the two tables is a one-to-one relationship

Answer 1

Just make it point to the PRIMARY KEY of the referenced table:

PO_Table (POId PRIMARY KEY, MainPharmacyID, SupplierID, PreparedBy)
PO_Items_Table (POItemID, POId FOREIGN KEY REFERENCES PO_Table (POId), ...)

Actually, in your PO_Table I don't see any other candidate key except POId, so as for now this seems to be the only available solution to me.

What are the "two options" you are considering?

Update:

Putting POItemID in the PO_Table is not an option unless you want your orders to have no more than one item in them.

Just look into it: if you have but a single column which stores the id of the ordered item in the order table, where are you going to store the other items?

Update 2:

If there is a one-to-one relationship, normally you just merge the tables: combine all fields from both tables into a single record.

However, there are cases when you need to split the tables. Say, one of the entities is rarely defined but has too many fields.

In this case, you make a separate relation for the second entity and make its PRIMARY KEY column also a FOREIGN KEY.

Let's imagine a model which describes the locks and the keys, and the keys cannot be duplicated (so one lock matches at most one key and vice versa):

Pairs (PairID PRIMARY KEY, LockID UNIQUE, LockProductionDate, KeyId UNIQUE, KeyProductionDate)

If there is no key for a lock or no lock for a key, we just put NULLS into the corresponding fields.

However, if all keys have a lock but only few locks have keys, we can split the table:

Locks (LockID PRIMARY KEY, LockProductionDate, KeyID UNIQUE)
Keys (KeyID PRIMARY KEY, KeyProductionDate, FOREIGN KEY (KeyID) REFERENCES Locks (KeyID))

As you can see, the KeyID is both a PRIMARY KEY and a FOREIGN KEY in the Keys table.

You may want read this article in my blog:

• What is entity-relationship model?

, which describes some ways to map ER model (entities and relationship) into the relational model (tables and foreign keys)

Answer 2

You don't have two options.. A Foreign Key constraint must be attached to the table, (and to the column) that has has the Foreign Key in it. And it must reference (or point to ) the Primary key in the other table. I don't quite understand what you mean when you say you have done this a number of times either way... What other Way ??

Answer 3

It looks like your PO_Table is the logical parent of the PO_Items_Table, which means the primary key of the PO_Table should be used as the Foreign Key in the items table

Answer 4

If PO stands for "Purchase Orders" and PO Item stands for a single line item of a purchase order, then you only have one choice about how to set up foreign keys. There may be many items for each purchase order, but there will only be one purchase order for each item. In this case, Quassnoi gave the correct design.

As a sidelight, every time I have designed a purchase order database, I have made the Items table have a compound primary key made up of POID and ItemID. But ItemID is not unique among all Items, just the items that belong to a single PO. Each time I start a new PO, I begin all over again with ItemID equal to one. This permits me to reconstruct a purchase order later on, and get the items in the same order as they were in when the order was first created. This is a trivial matter for most data processing purposes, but it can drive customers nuts if they look atr a PO later on, and the items are out of sequence, as they perceive sequence.