What is the most efficient way to store an unknown number of records in a database?

Question

What is the best way to store data if I don't know in advance how much data I might have?

The application is that I want to keep track of the employment history of a bunch of people, but I don't know how many places each person has worked. Would it be best to just alot the proper space for an arbitrary maximum of jobs or store it some other way?

I thought about storing the jobs in another table like this; would that be ideal?

score 1 · Accepted Answer · answered Oct 27 '11 at 19:44

1

Yes,this is "first normal form" in action

You'd have a Person table

PersonID
FirstName
LastName
..

and a Job table

PersonID (FK, PK)
JobStartDate (PK)
JobFinishDate
Title
Salary
...

Have a look at the free data models here for inspiration

answered Oct 27 '11 at 19:44

gbn

422,506
82
585
676

score 0 · Answer 2 · answered Oct 27 '11 at 19:37

0

It sounds like a typical 1 to N relationship.

You have a table that is called "Employees", and a table called "Jobs".

Your job table looks as follow :

| id | employee_id | job_title | job_infos... | current_flag | start_date | end_date |

answered Oct 27 '11 at 19:37

Dominic Goulet

7,983
7
28
56

score 0 · Answer 3 · answered Oct 27 '11 at 19:42

Would it be best to just alot the proper space for an arbitrary maximum of jobs

Yes. Always try to avoid building arbitrary limitations into your schema. Stay away from this sort of thing:

create table employee
(
    id int primary key,
    name text,
    job1_employer text,
    job1_position text,
    job2_employer text,
    job2_position text
);

And instead do something like this:

create table employee
(
    id int primary key,
    name text,
    ...
);

create table job_history
(
    id int primary key,
    employee_id int references employee,
    employer text,
    position text,
    ...
);

So, you've got one row for each employee, plus one row for each employee's past jobs.

score 0 · Answer 4 · answered Oct 27 '11 at 19:50

CREATE TABLE `employee` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `employment_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

You probably would want to look at adding a foriegn key between the employment_history.employee_id and employee.id as well.

This structure will let you add as many history records for a given employee without needing to denormalize your data. If you care about employers, you may also find that employment_history.name would be replaced by a employment_history.employer_id with the corresponding employer table setup. This'll let you look at how many past and present employees a company has as well.

What is the most efficient way to store an unknown number of records in a database?

4 Answers4