felito — 2011-05-08T10:42:42-04:00 — #1
I have this ERR (it is bigger but for the problem is irrelevant).
The users can be company's or single users (persons).
In the job_offers i have two foreign keys, so i used them as PK.
It has any problem with this model? the relation between users and company it is identified? at the moment they are represented as identified.
speda1 — 2011-05-08T13:10:25-04:00 — #2
So a user has many companies and a company has many job offers. Is the join between user and companies necessary or can a single user access all companies?
scallioxtx — 2011-05-08T13:27:49-04:00 — #3
Right now you've encoded it such that a user can have multiple companies, so the relation user:company is 1:N. I'd suggest you make that relation N:M so a user can have multiple companies, and a company can have multiple users, as that's more realistic.
To do this you need an extra table with the PK of users and the PK of job_offers as its PK.
If you don't want to do that: yes, your scheme is incorrect. The fact that users_id_user is a PK in the company table is in the PK doesn't make any sense, and that your PK in the job_offers table makes even less sense (did you notice that a company can have no more than 1 offer the way you've set it up!?).
I'd say that users_id_user in the company table should be in the primary key, but in the foreign key, and then put company_id_company in as an FK in the job_offers table and create and id_job_offer as the PK in that table.
PS. Please make up your mind about using singular or plural. Right now you have a table "users" (plural) with a field "id_user" (singular), a table "company" (singular), and "job_offers" (plural).
If you keep mixing it like this it come will come back to bite you in the behind one day.
felito — 2011-05-08T17:29:05-04:00 — #4
i will try to explain what i intend. can exists two types of users. Company users and single users (persons).
The single users can be students or workers. Company users can have multiple job_offers but each job_offer only belongs to a single company. The relation is one to one between users and company because the company user "siemens" only can have a year of foundation or a description of activity.
So, the table users have a group of fields that can be shared between two types of users- two single users (students and workers) and company users.
scallioxtx — 2011-05-08T17:36:02-04:00 — #5
If the relation between users and company is 1:1, company should have a field users_id_user that is the PK of that table, and is an FK to users.id_user
The way you've set it up now is not really 1:1, although you did try by putting the users_id_user in the company table, but that only makes things awkward ...
felito — 2011-05-08T17:48:03-04:00 — #6
i have what you suggest, users_id_user is PK of company and foreign key of users table.
What i tried is a generalization basically. The table users have fields shared between single user and company user, single user have fields shared between workers and students, and finally workers and particular fields and students too.
do you think that is a bad practice my logic?
scallioxtx — 2011-05-08T18:25:20-04:00 — #7
Okay, I meant users_id_user should be the only PK of the company table. You don't need the company_id and it only makes things confusing.
The rest of your logic for sharing fields is sound, yes
felito — 2011-05-08T18:26:17-04:00 — #8
thanks for your help