Handling Composite Keys Which are also Foreign Keys

Many-to-many relationships are usually resolved by having another junction table, which includes the keys from both sides of the relationship. This is a common scenario in database design. But things get slightly more complex when this junction table is referred by another entity.

Lets consider the below scenario:
There is a shop which purchases items in bulk, and resell them in smaller quantities. For example, they buy pens in units of Cartons and sell in smaller packs and pieces. Price of a unit varies as the measurement unit varies. A pack of pens has a different pricing (strategy) than an individual/piece of pen.
An invoice is issued for every sales which contains list of items, their relevant quantities and prices.

Lets say you came up with the following design to solve the above problem.

In the above diagram, Invoice_item_unit is more interesting as it represents a relationship between a junction table (item_unit) and an entity (Invoice). In Invoice_item_unit table, item_id and unit_id and invoice_number together form the the composite key which are actually foreign keys from other tables.

The first confusion here would be "Should item_id in Invoice_item_unit reference Item table, or Item_unit table?". It should be Item_unit, as that is what is involved in the relationship, not the original Item table.

The next confusion that may arise would be, since item_id and unit_id are from the same table, how do you define the SQL for the foreign key relationship. My emphasis is more on syntax, rather than concept. The solution is listed below.

ALTER TABLE invoice_item_unit ADD CONSTRAINT fk_item_unit_invoice_item_unit FOREIGN KEY (item_id, unit_id) REFERENCES `item_unit` (item_id, unit_id) ON DELETE RESTRICT,
 ADD CONSTRAINT fk_invoice_invoice_item_unit FOREIGN KEY (invoice_number) REFERENCES `invoice` (number) ON DELETE RESTRICT;

If you are using schema.yml of doctrine ORM to generate the database, handling the above underlined part may not be obvious at once. Here, is the solution.

  relations:
    ItemUnit:
      local: item_id
      foreign: item_id
      type: one
    ItemUnit_2:
      class: ItemUnit
      local: unit_id
      foreign: unit_id
      type: one
    Invoice:
      local: invoice_number
      foreign: number
      type: one

Update: The above was found using the reverse generation technique; create the tables and then create schema. But this approach seems problematic as doctrine in symfony 1.4 does not properly support this kind of a relationship. A better solution would be to assign a unique identifier to the item_unit table and refer that id in the invoice_item_unit table. This would work in symfony. Please refer the updated post to get a better picture on this.