I have two tables tbl_events with a column location_id. Then I have a table specifically to hold locations and their IDs,the columns in table tbl_locations are location_id and name. I want the location_id column in tbl_events to be a SET or ENUM such that when a new event is being entered, the values are the names of the locations but however whats put in the table is the location_id of the corresponding name value that was selected.
How can I achieve this as I am very new to Database design. I am using MySQL through PHPmyadmin

  • 1
    Don't use SETor ENUM types - they are evil :-). Joking apart, they really are! They breach Codd's rules - in particular, No. 2, the guaranteed access rule! – Vérace Jul 14 '16 at 20:25
  • just read the evil article...very interesting. so how do I achieve the referenced table solution for my problem? @Vérace – Manny265 Jul 14 '16 at 20:42
  • 1
    Have a lookup table - I think the article explains that - or use a decent database like PostgreSQL. Lookups are not the overhead that newbie programmers think they are - and far better. – Vérace Jul 14 '16 at 20:48
  • I think essentially I have that in the tbl_locations table but I want the user to see the name of the location when entering the event in the tbl_events table but what is saved is the location_id. @Vérace – Manny265 Jul 14 '16 at 21:06
  • That's called a lookup-combobox or similar - you can do this behind the scenes in SQL or using a "component" if you're designing a GUI. – Vérace Jul 14 '16 at 21:14

If there are a small number of locations, get rid of the locations table, and simply use an ENUM(...) for the locations. Then no id is needed, and you can use the names directly in events.

This assumes an event can have only one location. It it can have multiple locations, then ENUM won't work, and SET may be useful.

(No, I don't see ENUM as all that "evil.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.