0

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 other to users. Is there a problem with this? Does there exist another way to accomplish the same task?

EER

dgilperez
  • 10,716
  • 8
  • 68
  • 96
anvd
  • 3,997
  • 19
  • 65
  • 126

4 Answers4

2

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".

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
1
  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.
Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
1

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.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • 1
    Tables having a `PRIMARY KEY` is not normalisation (though, looking at the scenario from the other direction, it's difficult to do normalisation without one). – Lightness Races in Orbit May 06 '11 at 01:32
  • the table have a primary key that is composed by company_id_company and company_users_id_user. So, i can delete the id, correct? – anvd May 06 '11 at 01:41
  • @Tomalek. Normalization is not a one step process. The first step is to ensure keyed access to all data. This can't be acheived without guaranteeing that no two rows will contain the same data. A primary key is one way to make that guarantee. So it is a step towards normalization. – Walter Mitty May 06 '11 at 10:09
1

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)
Community
  • 1
  • 1
Ken Chan
  • 84,777
  • 26
  • 143
  • 172
  • the job_offers is a table that save job offers from a company. Like: Job: designer, job: commercial director. The system have two types of users: normal user (person) and company user. – anvd May 06 '11 at 02:57
  • Then , what is job_offers.company_user_id_user means? Does it mean 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? – Ken Chan May 06 '11 at 03:14
  • Then your design cannot handle the situation when a company user just post a job and before a normal user gets this job offer or no one gets this job offer eventually . I have updated my answer to handle this situation. :) – Ken Chan May 06 '11 at 03:42