Usage of Primary and Foreign keys in an EER diagram

In my db I have three tables (I have more but for case is equal, users can be companies or single people).

• Users has a primary key id_user;
• Company has a primary key id_company and a foreign key users_id_user;
• job_offers has a primary key id_job_offers and two foreign keys: company_id_company and company_users_id_user.

My questions are:

1. Does a primary key make sense in job_offers? I don't think that there is a reason for it.
2. job_offers has two foreign keys, one related to company and 开发者_如何学Pythonother to users. Is there a problem with this? Does there exist another way to accomplish the same task?

---

All tables should have a primary key. It sounds like you are asking whether your primary key should be a surrogate key or a natural key.

You might ask the same question of your other tables as well. For instance, assuming the email column in your users table is required and unique, it could be used as a (natural) primary key.

This question is pretty heavily debated, and both approaches can work (as can a mixed approach). If you want to read up on this subject in general, do a google search for "Natural vs. Surrogate Key".

---

Does a primary key make sense in job_offers? I don't think that there is a reason for it.

Yes . I agree that every table should have their own PK. Should each and every table have a primary key?

I have more but for case is equal, users can be companies or single people

job_offers has two foreign keys, one related to company and other to users. Is there a problem with this? Does there exist another way to accomplish the same task?

The system have two types of users: normal user (person) and company user. The job_offers is a table that save job offers from a company. If a company user want to post a job , a record will be inserted to the job_offers table . Then once the normal user get this job offer , the job_offers.company_user_id_user will be assigned to this normal user 's userid.

But from your ER diagram , Company.users_id_user is the PK , which cannot be null , and this PK is used in the job_offers.company_users_id_user as a FK. So job_offers.company_users_id_user also cannot be null .

As a result , it cannot handle the situation that a company user just post a job and before a normal user gets this job offer or no one gets this job offer eventually .In this case, job_offers.company_users_id_user should set to null , which violates the job_offers.company_users_id_user 's not null constraint.

I will accomplish the same task using this design:

Users
=================
id_user (PK)
email 
activation
password

Company
=================
id_company (PK)
activities 
foundation 
user_id (FK to Users)
description

job_offer
=================
id_job_offer (PK)
id_company (FK to Company)
description_offer 
tags

user_offer
=================
id (PK)
user_id (FK to Users)
job_offer_id (FK to job_offer)

---

1) make sense a primary key in job_offers? I think there is no reason

Yes there is - every table ought to have a primary key. It's called 'normalization.'

Your choice might not be very good. I'd say that the two foreign keys together should be the primary key, not the id column.

2) The job offers have two foreign keys, one related to company and other to users, any problem ? exists another way (best way) to make this?

No, that's how many-to-many relationships are done.

---

1. I think you're right. There is no need for a separate id field there. The two foreign keys should, together, make up the table's primary key.
2. Looks fine to me.