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? что-то вроде

Таблица компаний

• CompanyId

Таблица Dept

• DeptId

Таблица сотрудников

• EmployeeId

Вложения

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

Я что-то здесь упускаю?