CODEWITHSUNDEEP
sqldatabase-designdata-modeling
001
001

Reputation: 65097

Database design....storing relationship in one table, and the data in another table?

I'm looking at this company's database design, and would like to know the purpose of their design, ie store relationship in one table and the data in another, why do this?

They have this,

EMPLOYEE

EMPLOYEE_DATA

Rather than this...

EMPLOYEE

...OR this...(employee can belong to many departments)

EMPLOYEE

EMPLOYEE_DEPARTMENT

Upvotes: 0

Views: 1404

Answers (5)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

That's a link table, or join table, or cross table.. lots of different names.

How would you assign an employee to two different departments with your design? You can't. You can only assign them to one.

With their design, they can assign the same ID to multiple departments by creating multiple records with the employee ID and different department ID's.

EDIT:

You need to be more specific about what you're asking. Your first question seemed to be asking what the purpose of mapping table was. Then you changed it, then you changed it again.. none of which makes much sense.

It seems now that you are asking what the better design is, which is a totally different question than what you originally asked. Please state specifically what question you want answered so we don't have to guess.

EDIT2:

Upon re-reading, if this is the actual design, then no.. It does not support multiple department id's. Such a design makes little sense, except for one situation. If the original design did not include a department, this would allow them to add a department ID without modifying the original EMPLOYEE_DATA table.

They may not have wanted to update legacy code to support the Employee id, so they added it this way.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332531

Purpose of design is determined by business rules.
Business rules dictate entity (logical model perspective) / table (physical model perspective) design. No design is "bad" if it is built according to the requirements that were determined based on business rules. Those rules can however change over time -- foreseeing such changes and building to accommodate/future-proof the data model can really save time, effort and ultimately money.

The first and third example are the same -- the third example has an extraneous column (EMPLOYEE_DEPARTMENT.id). ORMs don't like composite keys, so a single column is used in place of.

The second example is fine if:

  • employees will never work for more than one department
  • there's no need for historical department tracking

Conclusion:

The first/third example is more realistic for the majority of real-world situations, and can be easily customized to provide more value without major impact (re-writing entire table structure). It uses what is most commonly referred to as a many-to-many relationship to allow many employees to relate to many departments.

Upvotes: 1

squawknull
squawknull

Reputation: 5191

The only remotely good reason for doing this is to implement an extension model where the master table identifying unique customers does not include all the data for customers that is not always necessary. Instead, you create one core table with the core employee data and and extension table with all the supplementary fields. I've seen people take this approach to avoid creating large tables with many columns that are rarely needed. However, in my experience it's typically premature optimization, and I wouldn't recommend it.

In contrast to many responses, the model included does not support multiple departments per employee - it is not a many to many mapping approach.

Upvotes: 0

Fellmeister
Fellmeister

Reputation: 591

If an employee can be in more than one department, then you would need a mapping table but I'd do it like the following:

EMPLOYEE
Id (PK)
First Name
Last Name


DEPARTMENT
Id (PK)
Name

EMPLOYEE_DEPARTMENT
EmployeeId_fk (PK)
DepartmentId_fk (PK)
Position

This would allow for multiple positions in multiple departments.

Upvotes: 1

mjec
mjec

Reputation: 1817

You would do this if an employee can be a member of multiple departments. With the latter table, each employee can only belong to one department.

Upvotes: 0

Related Questions