Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to design this database scenario?

Requirement is to store attachments for different entity types.

Say we have 3 entity types Company , Department and Employee. Each can have multiple attachments (documents).

Which is the best way to handle this?

Solution 1:

Company table

  • CompanyId

Dept table

  • DeptId

Employee table

  • EmployeeId

AttchmentType table

  • TypeId
  • Types (company, dept, employee)

Attachments table

  • AttachmentId
  • TypeId (maps to attachment type)
  • entityId (maps to CompanyId / DeptId / EmployeeId)

Pros: I can add new entity types easily in future

Cons: In this case I can't have foreign key relationship maintained between entities and attachments.

Solution 2:

Company table

  • CompanyId

Dept table

  • DeptId

Employee table

  • EmployeeId

CompanyAttachments table

  • AttachmentId
  • CompanyId (FK)

DeptAttachments table

  • AttachmentId
  • DeptId (FK)

EmployeeAttachments table

  • AttachmentId
  • EmployeeId (FK)

Pros: Foreign key integrity

Cons: In order add new entity I need to have new attachment table separately.

So which is the best way to go with assuming I may need to add new entities in future?


Edit 1:

Thanks for your reply guys.

If I want to go with solution 2, I see that creating new columns in attachments table easier instead of creating new attachment tables for every entity just to map them? something like,

Company table

  • CompanyId

Dept table

  • DeptId

Employee table

  • EmployeeId

Attachments

  • AttachmentId
  • CompanyId (FK)
  • EmployeeId (FK)
  • DepartmentId (FK)

am I missing something here?

like image 809
puzzled Avatar asked Aug 04 '10 17:08

puzzled


People also ask

How do you design a database example?

There are mainly two tools you'd use to create a database schema. The first is an RDBMS, or a relational database management system. Examples of RDBMSs include SQL Server, MySQL, and PostgreSQL. The second and most important tool is SQL (structured query language).

What are the 3 database design steps?

The methodology is depicted as a bit by bit guide to the three main phases of database design, namely: conceptual, logical, and physical design.


1 Answers

I'd definitely go with solution #2. Your one pro for solution #1 isn't really a pro. If you add a new entity you're going to necessarily have to already add a new table for that entity and you'll already be adding or changing existing code to handle it. You should be able to make some generic objects that handle the pattern so that duplicated code isn't a problem.

like image 126
Tom H Avatar answered Sep 24 '22 17:09

Tom H