CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designrelational-database
David542
David542

Reputation: 110277

Preferable database design for job posts

I have two types of job posts -- company job posts and project job posts. The following would be the input fields (on the front-end) for each:

*company*
- company name
- company location
- company description

*project*
- project name
- project location
- project description
- project type

What would be best database design for this -- one table, keeping all fields separate -

`job_post`
- company_name
- company_location
- company_description
- project_name
- project_description
- project_type
- project_location

One table combining the fields -

`job_post`
- name
- location
- description
- project_type
- is_company (i.e., whether a company (True) or project (False)

Or two tables, or something else? Why would that way be preferable over the other ways?

Upvotes: 0

Views: 305

Answers (3)

Mike Purcell
Mike Purcell

Reputation: 19989

You have identified 3 major objects in your OP; the job, the project, and the company. Each of these objects will have their own attributes, none of which will are associated to the other objects, so I would recommend something akin to (demonstrative only):

job
  id
  name

company
  id
  name

project
  id
  name

link_job_company_project
  job_id
  company_id
  project_id

This type of schema will allow you to add object specific attributes without affecting the other objects, yet combine them all together via the linking table into a 'job post'.

Upvotes: 2

Kuntady Nithesh
Kuntady Nithesh

Reputation: 11721

This surely got to do with volume of data stored in the table . In an abstract view one table looks pretty simple . But as Ryan says what if requirements change tomorrow and more columns to be added for one type either company or project. If you are with the prediction that large amount of data top be stored in the table in future even I will prefer 2 tables which will avoid unnecessary filtering of large amount of data which is a performance overhead.

Upvotes: 1

Ryan Kempt
Ryan Kempt

Reputation: 4209

Depending on a lot of factors including the maximum size of this job, I would normalize the data even further than 2 separate tables, perhaps having a company name table, etc... as joining multiple tables results in much faster queries than one long never ending table full of all of your information. What if you want to add more fields to projects but not companies?

In short, I would definitely use multiple tables.

Upvotes: 5

Related Questions