Giza Giza - 1 year ago 51
MySQL Question

Object storage in mysql

I am working on a database design where job-seekers must list their previous work experience.

Work experience is a class that has:


  • The employer

  • The position held



I would like to model the classes and show the link between a jobseeker and their previous work experience.

I was thinking of creating a table for previous work experience and referencing it to a job-seeker, but each job-seeker can have multiple entries for work experience and this could get messy.

Another way to do it would be to store all the work experience of the users as a list in the jobseeker table.

I'm not sure which to go with and would like some advice on which is acceptable and which is not. Any other alternatives are welcome and in fact requested. Thanks

Answer Source

I would go with something like this set-up.

This will hold all the users past experience

CREATE TABLE `positions` (
  `position_id` int(11) NOT NULL AUTO_INCREMENT,
  `position_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`position_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

This will hold the users personal data

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_firstname` varchar(20) NOT NULL,
  `user_lastname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This will hold users work experience - a "middle" table for positions and users.

CREATE TABLE `work_experience` (
  `seeker_id` int(11) NOT NULL AUTO_INCREMENT,
  `seeker_position` int(11) NOT NULL,
  `seeker_employer` varchar(20) NOT NULL,
  `seeker_user` int(11) NOT NULL,
  PRIMARY KEY (`seeker_id`),
  KEY `positon_id` (`seeker_position`),
  KEY `user_id` (`seeker_user`),
  CONSTRAINT `user_id` FOREIGN KEY (`seeker_user`) REFERENCES `users` (`user_id`),
  CONSTRAINT `positon_id` FOREIGN KEY (`seeker_position`) REFERENCES `positions` (`position_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

It would then function like;

SELECT users.* FROM users;
+---------+----------------+---------------+
| user_id | user_firstname | user_lastname |
+---------+----------------+---------------+
|       1 | bob            | bob           |
+---------+----------------+---------------+
1 row in set

SELECT positions.* FROM positions;
+-------------+-------------------+
| position_id | position_name     |
+-------------+-------------------+
|           1 | stackoverflow guy |
+-------------+-------------------+
1 row in set

SELECT work_experience.* FROM work_experience;
+-----------+-----------------+-----------------+-------------+
| seeker_id | seeker_position | seeker_employer | seeker_user |
+-----------+-----------------+-----------------+-------------+
|         1 |               1 | StackExchange   |           1 |
+-----------+-----------------+-----------------+-------------+
1 row in set

User "1" (bob) has had work experience at "StackExchange" being a "stackoverflow guy"

enter image description here

  • work_experience.seeker_user references users.user_id
  • work_experience.seeker_position references positions.position_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download