CakePHP数据库设计：工作板的关联

Question

Hy everyone.

I'm actually building a job board with CakePHP and a little help for designing the database will be appreciated!

I have a table jobs with differents foreigns keys: id, recruiter_id, title, sector_id, division_id, experience_id etc.

The associated table (sectors, divisions and experiences) have the same configuration id, name and job_count and sometimes on or two other fields (like company_count for sectors).

So I would like to know if there is better way to design these tables. I thought for putting the three of them in one table named lists with the keys: id, value and list_name. With this configuration I have just one request to do to get all the list and not 3.

My question is what is the "good way" solution ? May be there's another one ?

Answer 1

I think a found a solution by using a system of taxonomy. I created a table terms which contain the list of all terms that can be associated (sector, division, type of contrat, etc.).

Table terms id, name, type

And I created a second table term_relationships which contain all the association including the name of the model that is associated.

Tabe term_relationships id, ref, ref_id, term_id

"ref" refers to the associated model (example: Job or Applicant in my case), the "ref_id" refers to the associated data (which job or which applicant) and term_id refers to which terms is associated. I think is the most evolutive and cleaner solution.

Thanks all for your help (especially Grafikart from where I get the idea) and hope that this topic can help someone else !