0

I am working on employee management system project in laravel in which the requirement is to store the employee information with as much column as the user wants. I cannot remain table columns fixed. Admin has the functionality to add employee information with its own created columns like Name: Khizer, Age: 20 etc.

So I managed this scenerio in the following way:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

    **Employee Table:**
    ID      created_at      updated_at
    12      2020-9-21       2020-9-21
    23      2020-9-21       2020-9-21

CREATE TABLE `columnvalues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `employee_column_id` int(11) DEFAULT NULL,
  `column_value` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  
  CONSTRAINT `fk_column` FOREIGN KEY (`employee_column_id`) REFERENCES `employee_columns` (`id`),
  CONSTRAINT `fk_emp` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`)
)
    **ColumnValues**
    ID  EmployeeId   Column_Id    Value
    1   12           20           Khizer
    2   12           21           20

CREATE TABLE `employee_columns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `columnname` varchar(200) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
    **Columns Table**
    ID    Column_Name
    20    Name
    21    Age

I created three tables to store as much value as needed. 1st employee, 2nd columns and third is the associative entity between employee and columns. Is this better solution or there is other option to handle this scenrio?

Hammad Butt
  • 73
  • 2
  • 7
  • EAV is useful pattern for your task. – Akina Mar 11 '22 at 10:36
  • Using dynamic number of properties is at odds with relational database design, therefore all options have drawbacks. See Bill Karwin's answer to the duplicate question for a comprehensive list of options you have. All of them can work for you, but you have to make the call which approach you choose. However, knowing a little bit about HR systems, the employee table can be designed with a fix number of columns as there are requirements for payroll and other legal reasons what data you need and can store about employees. I cannot imagine a scenario when you don't want to store an employee's name – Shadow Mar 11 '22 at 10:44

0 Answers0