database - How to store id of a record instead of text in parent table -

i have input users regarding skills. have table skills has id primary key. in other table storing user id , skill id many many relationship. problem how know skill entered user in skills table? because have put id of skill in many many relationship table. run each time select statement or there efficient solution available? thanks,

how know skill entered user in skills table?

in concurrent environment, there no way know that. if select, tells whether row existed @ time of select execution - doesn't tell whether row exists now. example, if select returned empty result, concurrent transaction might have inserted row within few milliseconds took receive select result.

so either drastic reduction in concurrency (e.g. through table locks), or learn live it...

when insert needed

i'd recommend attempt insert (without select) , ignore possible primary key violation¹.

if did separate select , insert steps, you'd still have prepared pk violations, since concurrent transaction perform insert (and commit) after select before insert. why bother select in first place?

when insert or update needed

if junction table contains other fields in addition fk fields, might want update them new values, you'd have first perform select determine if row needs inserting or updating.

in such case, consider locking row using select ... update (or equivalent syntax).² alternatively, dbmses offer "insert or update" (aka "upsert") in single command (e.g.: mysql insert ... on duplicate key update).

---

¹ careful only ignore pk violations - don't blindly "swallow" fk or check violations etc...

² avoid being deleted before had chance update (insert pk violation still possible). worse, concurrent transaction update row, leaving transaction silently overwrite other transaction's values, without being aware ever there.