'mysql table relationship with primary and foreign keys

I have created 3 tables: accounts, products, claims. These are the relationships:

  • Accounts: PK - username
  • Products: PK - serial number, FK - username
  • Claims: FK - username, FK - serial number

My issue is that a user can add a claim even for products the user has not purchased, as long as the user knows that serial number, while I should allow the user to add a claim only for products the user purchased. For inserting claims I am using this query:

INSERT INTO claims (username, serial_no, date, issue) VALUES (%s, %s, %s, %s)

Do I need to change the table relationship with keys to fix the above, or use a more specific query? Better, what is the logic behind how this is supposed to work?

mysqlinsertkeyrelationship


Solution 1:[1]

You should filter the products by username of the current user to avoid claiming products that are not related to the user. If you filter the product to be claimed then you won't need to change your table structure, it's only up to you on how you'll implement the filter in the programming language you are using.

First select all the products related to the user.

SELECT * FROM products WHERE username=@currentusername;

Then in your program, just allow the user to claim only from the fetched products.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions