Make a (composite) primary key for an 'item' table where the incremental 'item_id' resets per 'order_id' foreign key

Question

Teacher wants us to discuss how to implement the 'line_item' table of a receipt database for a global business. He wants us to discuss the primary (composite?) key of such a 'line item' table. If our global business is printing thousands of receipts (with hundreds of items) every minute, how much do we need to consider the possibility of hitting the upper limits of an INT for the line_item_id? Is there a more effective way to handle the line_item id?

"Note: Gaps in numbering (generally caused by deletions) are okay. Also look for possible race conditions."

Consider this: You add 3 receipts with 3 items each. You delete the 2nd item of the 3rd receipt. Then add a new item to the 3rd receipt.

So I'm picturing something like this to start with: (Where receipt #1 and #3 were originally identical before the add/delete exercise. Note the numbering on #3.)

|  receipt_id (FK)  |     line_id (INT)    |  line_text (TINYTEXT)  |
---------------------------------------------------------------------
|         1         |           1          |  'Apple'               |
|         1         |           2          |  'Banana'              |
|         1         |           3          |  'Coconut'             |
---------------------------------------------------------------------
|         2         |           1          |  'Apple'               |
|         2         |           2          |  'Apple'               |
|         2         |           3          |  'Apple'               |
---------------------------------------------------------------------
|         3         |           1          |  'Apple'               |
|         3         |           3          |  'Coconut'             |
|         3         |           4          |  'Dates'               |
---------------------------------------------------------------------

We've been learning SQL and PHP (mySQLi in innoDB if that matters much.) However I feel this may be accomplished in SQL alone, right?

It appears I should use the MAX() function in the second column to make a COMPOSITE PRIMARY KEY and to avoid worrying about hitting the upper INT threshold.

I'm trying to concoct a MySQL INSERT that would do something dynamic with its numbering of the receipt line, like:

INSERT INTO line_item (3, VALUES MAX(receipt_line)+1, 'Dates')

At the end he has other thoughts like: "What if you now delete and add an item at the end of the second receipt?"

I imagine these are the operations he's looking for:

-- Delete the last line of the second receipt
DELETE FROM line_item WHERE receipt_id=2 AND line_id=MAX(line_id)

-- Add a new line to the second receipt.
INSERT INTO line_item VALUES ( 2, MAX(line_id)+1, 'Watermelon')

And I expect results like:

|  receipt_id (FK)  |     line_id (INT)    |  line_text (TINYTEXT)  |
---------------------------------------------------------------------
|         1         |           1          |  'Apple'               |
|         1         |           2          |  'Banana'              |
|         1         |           3          |  'Coconut'             |
---------------------------------------------------------------------
|         2         |           1          |  'Apple'               |
|         2         |           2          |  'Apple'               |
|         2         |           3          |  'Watermelon'          |
---------------------------------------------------------------------
|         3         |           1          |  'Apple'               |
|         3         |           3          |  'Coconut'             |
|         3         |           4          |  'Dates'               |
---------------------------------------------------------------------

But instead I get a bunch of errors and weird results.

Do I need to use PHP to calculate the line_id instead? What am I missing?

Answer 1

You can't use just MAX(line_id) because that gets the maximum value of line_id across all receipts. What you want is the maximum line_id in one specific receipt. Something like SELECT MAX(line_id) FROM line_item WHERE receipt_id = 3.

You could build that into a single SQL query using a sub select to get the new line_id value. But I think it would be better done in PHP. Determine the new line_id first, then insert your new line item. In two separate queries.

Also think about how your receipt application will delete a line item. If I want to delete the second Apple line item in receipt 2 I would need to know it's line_id. So your application will need to know the line_id of new items it adds.