MySQL Forums
Forum List  »  Newbie

Looking up a value in a 'subtable' and inserting it if it doesn't exist
Posted by: Dave Kennard
Date: April 29, 2009 09:47AM

In my database I have one main table, imageData. Then for columns in imageData that only have a few possible values or the same value will be reoccuring often, I have created separate 'subtables' to hold these values:

Now when I want to insert a row into the imageData table, I have to look up the correct key for each value where the value is stored in a 'subtable'. If the value doesn't already exist, then I'll need to insert it.

I can think of two ways to do this - INSERT IGNORE all the subtable values into the subtables, then when updating the imageData table use subqueries to get the ids for the values stored in subtables. e.g.
INSERT IGNORE INTO cameras SET camera = 'my camera'; etc.
INSERT INTO imageData SET camera = (SELECT id FROM cameras WHERE camera = 'mycamera'), etc.

Or otherwise use a stored procedure that selects the ids of the subtable values and stores them as variables for use when inserting into imageData. If the values didn't exist, it would have to insert them and give the variable the value of LAST_INSERT_ID().

So 2 questions:
1. Is my schema sensible?
2. If it is, what would be the best way to handle this scenario?



Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.