Linking tables

[jobs1109]

Hi I got three tables (employers , company ,  and  Jobs) Employer table holds info about employer. Company hold info about the company and jobs table holds info about jobs. I was just wandering what would be a good way to link these tables in the database ?  Here is the coding for each table.

 

 

Employer Table

 

CREATE TABLE IF NOT EXISTS `employers` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

Jobs Table

 

CREATE TABLE IF NOT EXISTS `Jobs` (
  `id` int(11) NOT NULL,
  `JobTitle` varchar(200) default NULL,
  `Company` varchar(200) default NULL,
  `Salary` varchar(30) default NULL,
  `Description` varchar(2000) default NULL,
  `CompanyURL` varchar(200) default NULL,
  `PhoneNumber` varchar(30) default NULL,
  `Requirements` varchar(2000) default NULL,
  `JobCategory` varchar(100) default NULL,
  `JobType` varchar(100) default NULL,
  `Apply_To` varchar(1000) NOT NULL,
  `Email` varchar(200) NOT NULL,
  `modified_at` datetime NOT NULL,
  `PostedOnDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Address` varchar(250) NOT NULL,
  `State` varchar(200) NOT NULL,
  `City` varchar(200) NOT NULL,
  `Country` varchar(100) NOT NULL,
  `Zipcode` varchar(100) NOT NULL,
  `JobID` varchar(100) NOT NULL,
  `WorkExperience` varchar(2000) NOT NULL,
  `EducationRequirement` varchar(2000) NOT NULL,
  `WebsitePostedFrom` varchar(200) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

Company Table

 

CREATE TABLE IF NOT EXISTS `Company` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `CompanyName` varchar(100) NOT NULL,
  `Address` varchar(100) NOT NULL,
  `Logo` varchar(100) NOT NULL,
  `PhoneNumber` varchar(25) NOT NULL,
  `ContactPerson` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

[Buddski]

It depends how they are related to each other..

Are the employers assigned to a company? jobs assigned to an employer? and so on.

 

[jobs1109]

Hi yes employer are assigned to a company and jobs are assigned to a company. I am not sure how to implement the relationships.

[Buddski]

In your jobs and employer tables you need to create a column called something like, company_id.

Then whenever a job or employer is created you can put the company table's unique ID into the respective table..

Example.

A new job is created for "Bobs Shop" company, Bobs Shop has an ID of 53. When you create the job you enter in all the information and put 53 into the company_id field. Thus completing the relationship between the 2 tables

 

I also noticed that your job and employer tables do not have an auto incrementing id field, might be a good idea to add that.

[jobs1109]

Thanks for your help I will try it.