So I'm working on a MySQL database with three tables:
Table 1 defines an fruit, with columns like "name", "color", "flavor" and "Fruit_ID".
Table 2 defines a category, with columns like "name", "description", and "Cat_ID".
Table 3 defines the relationship between fruit and category, with two columns, "Fruit_ID" and "Cat_ID".
Whenever a fruit is inserted, it must be correlated to a category using Table 3. My original plan was to make Fruit_ID and Cat_ID autoincrement and use this method, but the problem is my architecture has multiple users sharing just one client connection, and LAST_INSERT_ID is only guaranteed to be correct for each client, afaik.
So my question is, what kind of SQL Query can I use to ensure that when a fruit is entered into Table 1, the correct Fruit_ID is used in Table 2 to categorize it?
Also, I'm open to restructuring my database if this isn't the best way to make this kind of association (I'm new to database architecture).
I'm not open to using stored procedures in SQL.
Thanks a lot in advance!
If you can't use LAST_INSERT_ID(), you'll have to use a query to get the ID of the item you just inserted.
So to add a row that says apples are red, you do:
INSERT INTO fruit_category (fruit_id, cat_id)
SELECT f.id, c.id
FROM fruit AS f
CROSS JOIN category AS c
WHERE f.name = 'apple'
AND c.name = 'red'
This is the same thing you would do if you were creating this relationship and hadn't just added the rows.