我想将员工经验数据存储在数据库中，员工可以一次添加多个经验。
数组：
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
)
）
我要做的是将数组转换为字符串，然后像这样存储在数据库中：
[start_date] = 2016-07-12,2016-09-16,
[end_date] =2016-09-09,2016-01-20
数据库结构：
我认为这不是正确的方法。请提出任何建议？
最佳答案
在数据库中存储逗号分隔的值通常会违反First Normal Form。有关更多信息，请参见this answer。
我建议使用两个表：一个用于员工，另一个用于经验。然后，您可以使用“经验”表中的外键来引用“员工”表中的主键。
为了示例，我简化了您的表结构：
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');
请注意，
experiences表具有一个名为“ employee_id”的外键。此值与employees表中员工的主键有关。使用此结构，您无需为每种体验重复员工数据。可以轻松添加新的体验并将其与现有员工相关联。您可以使用
JOIN提取关联的数据，具体取决于所需的输出。按日期升序获取所有体验：
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
计算每位员工的经验：
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
以下是一些有用的信息资源，您可能会有所帮助：
Database Normalization - Explained with Examples
Normalization in Database (With Example)
Normalization of Database
关于php - 如何在数据库中存储员工经验数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38443153/