MySQL Many to Many with Unique keys and Update/Select if Exists otherwise Insert
- by Jayrox
In my goal to have a Many-to-Many relationship in my MySQL database I have arrived at another bridge to build.
Current Tables:
Users (id, name)
Tags (id, name)
User_Tags (user_id, tag_id)
Here is the goal:
I would like to have the ability to take a tag i.e: #fb and insert it into my Tags database which has a unique constraint on name and if the tag exists I would like to return it's id
I would like to insert the tag.id and the current user's user.id into User_Tags. I do not have any unique constraints on the User_Tags table because I would like to be able to track the user's most commonly used tags and present them in a list ordered by frequency of use.
I am currently developing this project in PHP using prepared statements.
Additionally, performance is not currently an issue as my user count is still fairly small, but it is steadily growing and may be an issue in the future.
