CODEWITHSUNDEEP
sqldatabasedatabase-design
Tomuke
Tomuke

Reputation: 877

Database structure - How to link users to jobs?

I'm starting a project where people can sign up, login to their account and search for jobs base don their location and industry etc.

These are the tables I have identified I will need so far, however, I want each user to be able to apply for a job and keep track as to what jobs they have applied for.

For example, they click an 'apply' button on a job and it adds that specific job their account somewhere which will then allow me to run a query along the lines of "SELECT * jobs WHERE (this user has applied)".

tblUser:
-userID
-firstname
-surname
-dob
-email
-address

tblJob
-jobID
-Title
-Description
-Position (full time/part time)
-Salary
-DatePosted
-userID???? <--as FK?
 -LocationID
-industryID

tblIndustry
-IndustryID
-IndustryName

tblLocation
-LocationID
-LocationName

Would this be correct and work? Also, are there any suggestions on any extra functionality I could add?

Upvotes: 3

Views: 975

Answers (3)

alexandernst
alexandernst

Reputation: 15089

You need a relation of the type many to many, so just create a new table that will have the id from the job and the id from the user. Also, you need to make both primary keys.

EDIT:

Each time somebody applies to a job you insert in that table both the job's id and the user's id.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

You would probably want a table tblJobApplications with foreign keys to tblUser and tblJob tables.

You would query on it like

SELECT * {or pick your specific fields here}
FROM tblJobApplications AS ja
INNER JOIN tblJob AS j ON ja.job_id = j.job_id
WHERE ja.user_id = ?

Upvotes: 1

Randy
Randy

Reputation: 16677

you need a new table

user_applications
-----------------
user_id
job_id
application_dt
status

something like that. do not put he user id directly in the job table as you have shown.

Upvotes: 1

Related Questions