CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
aas
aas

Reputation: 197

Normalizing and composite table structure

I have difficulty to optimizing the database design in a situation

There is a table with 3000 employees and another one table with 300 departments.

Problem is there is a possibility to 3000 employees in 300 departments. Basically each employee will work in all department 300x3000 records (worse case). May be only 10 employees will work in 300 department (best case) and not a problem or have a composite table.

So what is the best way to structure

Please advice me

Thanks

Upvotes: 2

Views: 229

Answers (1)

Fabian
Fabian

Reputation: 2982

In a relational database, the appropriate pattern for this is to store the fact that an employee works in a department is a relation:

create table employees(employee_id, ..., primary key(employee_id));
create table departments(department_id, ..., primary key(department_id));
create table works_in(
  employee_id, 
  department_id, 
  primary key(employee_id, department_id),
  foreign key(employee_id) references employees(employee_id),
  foreign key(department_id) references departments(department_id)
);
/* Add column datatypes as needed */

This pattern is applicable in almost all cases, even for large relationship tables. If you are worried about space consumption, many databases can store this kind of table very efficiently, e.g., storing them physically as an index-organized table, e.g., storing them grouped by employee_id, etc.

However, even 300 x 3000 entries does not sound as if you would need such kind of physical database design optimization.

Upvotes: 3

Related Questions