CODEWITHSUNDEEP
sql-serverdatabase-design
puzzled
puzzled

Reputation: 89

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

Dept table

Employee table

AttchmentType table

Attachments table

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

Dept table

Employee table

CompanyAttachments table

DeptAttachments table

EmployeeAttachments table

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

Dept table

Employee table

Attachments

am I missing something here?

Upvotes: 8

Views: 3836

Answers (5)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

Hope this is self-explanatory.

attachment_model_v1

Upvotes: 4

Rawheiser
Rawheiser

Reputation: 1228

Other things to consider:

Are you going to need to roll up attachments? i.e. an employee's attachments are associated with his/her department and their company? if this is a frequent query a single attachment table and a separate and heavily index entity look-up table option may give better query performance.

Also, Are the attachments going to be many and/or huge enough that you need to put those table(s) on a separate device, or another storage system (i.e. file system pointers)? Management is an issue as well as performance.

Upvotes: 0

Martin
Martin

Reputation: 40365

I would go with option 2.

Something like this:

alt text http://img84.imageshack.us/img84/815/dbso.png

Upvotes: 2

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 5

Vidar Nordnes
Vidar Nordnes

Reputation: 1364

I vote for solution 2 because this way you can enforce referential integrity in a proper way. In addition you can easily (if needed) add fields for special attachments (for instance EmployeeAttachments might have a bit field "PersonalPicture" or similar)

Upvotes: 3

Related Questions