How to store employee experience data in database

Question

I want to store employee experience data in database ,employee can i add multiple experience at a time .

Array :

Array
(
    [start_date] => Array
        (
            [0] => 2016-07-12
            [1] => 2016-09-16
        )

    [end_date] => Array
        (
            [0] => 2016-09-09
            [1] => 2017-01-20
        )

    [total_month] => Array
        (
            [0] => 2
            [1] => 4
        )

    [firm_name] => Array
        (
            [0] => sadsadasd
            [1] => 34343
        )

    [turnover] => Array
        (
            [0] => 343443434
            [1] => 443434
        )

    [student_experiece] => Array
        (
            [0] => 343434
            [1] => 343434
        )

    [orderof_supply_food] => Array
        (
            [0] => 343443
            [1] => 434343434
            [2] => 34334
            [3] => 34343
        )

    [payment_against_bill] => Array
        (
            [0] => 3434343
            [1] => 434343
        )

    [vat] => Array
        (
            [0] => 3434343
            [1] => 3434343434
        )

)

What i do is convert array in to string and than store in database like this:

[start_date] = 2016-07-12,2016-09-16,
[end_date] =2016-09-09,2016-01-20

Database Structure :

i thing this not a right way .Please any suggestion?

Answer 1

Storing comma-separated values in a database generally violates the First Normal Form. For more information, see this answer.

I suggest utilizing two tables: one for employees and one for experiences. Then, you can use a foreign key in the "experiences" table to refer to a primary key in the "employees" table.

I've simplified your table structure for the sake of example:

CREATE TABLE IF NOT EXISTS `employees` (
  `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name_first` varchar(50) NOT NULL,
  `name_last` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `experiences` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_id` (`employee_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


INSERT INTO `employees` (`id`, `name_first`, `name_last`) VALUES
(1, 'Jane', 'Doe'),
(2, 'John', 'Doe'),
(3, 'Elmer', 'Fudd'),
(4, 'Tester', 'McTesterson'),
(5, 'Sally', 'Jones');

INSERT INTO `experiences` (`id`, `employee_id`, `date`, `description`) VALUES
(1, 2, '2016-07-01', 'Amazing Experience'),
(2, 4, '2016-07-02', 'Testing'),
(3, 2, '2016-07-03', 'Another Experience'),
(4, 3, '2016-07-05', 'Test Experience'),
(5, 1, '2016-07-07', 'Sample Experience'),
(6, 2, '2016-07-15', 'An experience');

Note that the experiences table has a foreign key named "employee_id". This value relates to the primary key of an employee in the employees table.

Using this structure, you'll not need to repeat employee data for each experience. A new experience can easily be added and associated with an existing employee. You can fetch the associated data using a JOIN, depending on the output you desire.

Fetch all experiences, in ascending order by date:

SELECT emp.`name_first`,emp.`name_last`,exp.`date`,exp.`description`
FROM `experiences` exp
LEFT JOIN `employees` emp ON (emp.`id`=exp.`employee_id`)
WHERE 1
ORDER BY exp.`date` ASC;

name_first  name_last   date        description 
John        Doe         2016-07-01  Amazing Experience  
Tester      McTesterson 2016-07-02  Testing 
John        Doe         2016-07-03  Another Experience  
Elmer       Fudd        2016-07-05  Test Experience 
Jane        Doe         2016-07-07  Sample Experience   
John        Doe         2016-07-15  An experience   

Working Example

Count experiences for each employee:

SELECT emp.`name_last`,emp.`name_first`,COUNT(exp.`id`) as `experience_count`
FROM `employees` emp
LEFT JOIN `experiences` exp ON (exp.`employee_id`=emp.`id`)
GROUP BY emp.`id`
ORDER BY emp.`name_last` ASC;

name_last   name_first  experience_count
Doe         John        3
Doe         Jane        1
Fudd        Elmer       1
Jones       Sally       0
McTesterson Tester      1

Working Example

---

Here are some informative resources you may find helpful:
Database Normalization - Explained with Examples
Normalization in Database (With Example)
Normalization of Database

Answer 2

You can use json_encode() to encode your PHP-Array to a JSON-String. When you get the JSON-String back from Database, you can decode it with json_decode() into an PHP-Array. Then you can modify it and store it back to database.