Database Design help needed

Question

I am trying to develop a database model for a recruitment website and I have a lot of confusions.

• Job seeker can make 5 resumes online. In location field he can add 5 locations at most.
• Job seeker can fill up a form and jobs will be emailed according to the fields that he filled up. In this form he can select as many locations as he want.
• Job Poster can Posts job. And he can add as many locations as he want in job form.

I have created a location table with all the locations. Now I am confused how locations will be saved in resume, jobEmail and jobAd table?

I can think of two solutions.

1. There will be a locations field in each table and all location IDs posted from forms will be saved in this field separated by commas. And later on I can use some mySql function to match these locations.
2. Create another table for each table with columns say resumeId, locationId, and all locations will be saved as a separate record in this table.

Which of these solution is right? Or is there an other way for this kind of scenarios.

Thanks

Answer 1

Create another table. Never, ever store data as comma-separated values. "Never, ever" might be a slight exaggeration but in most circumstances you'll be best served storing the data in a table so you can apply normal SQL queries to it without having to parse the comma separated data.

In other words, the second approach is the optimal one.

Answer 2

I would have all these "classifications" or "objects" as a seperate table. This will breed extensibility when the requirements change (they always do).

Then control the fact they are only allowed 5 through code, either in a stored procedure or via php, or c#, does not matter really.

Answer 3