Confused with table structure

Question

I'm working on an inventory system where I save information about products in MariaDB. The products are stored in a table 'article', and every product has a uniqe ID.

I have another table 'stocks', which holds information about articles, namely amount and insert date. The insert date is important, because it should be possible to have the same article in the database twice but with different insert dates.

For example, you buy two apples on Monday and put them in the database. On Wednesday, you again buy two apples and put them in the database as well. In total, you have 4 apples in the database, but in two separate rows and with different registration dates. This distinction is an important feature for my system because I want to be able to tell for how many days a product has been inside the database.

My tables look like this:

Article

+------------+--------------+----------+
| article_id | article_name | category |
+------------+--------------+----------+
|          1 | Shiny Apple  | Fruit    |
+------------+--------------+----------+

Stocks

+----------+------------+--------+-------------------+
| stock_id | article_id | amount | registration_date |
+----------+------------+--------+-------------------+
|        1 |          1 |      2 | 2017-10-23        |
|        2 |          1 |      2 | 2017-10-25        |
+----------+------------+--------+-------------------+

Now my Problem is that the auto-incrementing stock_id makes it possible to have the same article with the same registration_date twice, which I don't want.

For example, I can execute the following query multiple times and get multiple rows with the same date despite the fact that the registration date is part of the PK

INSERT INTO `stocks` (`article_id`, `amount`, `registration_date`)
VALUES (1, 3, CURDATE())
ON DUPLICATE KEY UPDATE amount = amount + 1

The intended behaviour is that if you insert an article multiple times on the same day, the 'amount' field should just be incremented instead of the insertion of a new row.

This would be possible if the stock_id wasn't part of the primary key, however then it wouldn't be auto-incremented because MySQL/MariaDB doesn't allow an auto-incremented field to not bet part of the PK.

Another option would be to remove the stock_id field altogether, but I kinda need it for convenience purposes, so I wonder if there was a better way.

I thought about solving this issue inside the program (check if article exists in stocks, the check the date etc.), but this seems unelegant to me.

I feel like there must be an obvious and easy solution, but I can't see it. Any help is appreciated.

Possible duplicate of [IF Exists Then Update MySQL](https://stackoverflow.com/questions/28856445/if-exists-then-update-mysql) — isaace, Oct 31 '17 at 15:21

score 2 · Accepted Answer · answered Oct 31 '17 at 15:25

2

I think you are looking for a unique index/constraint on the article and date:

alter table stocks add constraint unq_stocks_article_date unique (article, registration_date);

The update that you then want would look like:

INSERT INTO stocks (article_id, amount, registration_date)
    VALUES (1, 3, CURDATE())
    ON DUPLICATE KEY UPDATE amount = amount + values(amount);

The increment is based on the value passed into the insert.

answered Oct 31 '17 at 15:25

Gordon Linoff

1,242,037
58
646
786

At which point, get rid of `id` and have `PRIMARY KEY(article, registration_date)`. – Rick James Nov 05 '17 at 21:02

Confused with table structure

1 Answers1