central attachment/file table

Question

central attachment/file table

fritshoutsma

SSC Enthusiast

Points: 191
More actions
March 6, 2012 at 2:21 am

#269428

I am busy building a database, and now I have a question about my design.
I have got the following tables:
Customer
Employee
Project
Attachment
AttachmentType
I want to be able to add multiple attachments to Customers, Employees and Projects.
What is the best design for this?
Now I store the id of the customer, employee or project in the attachment table in a RefId column and based on the attachmenttype I can tell to which table the attachment links.
This design prevends me from using foreign keys in the attachment table.
Is this the best design, or are the better ways to do it?
Thanks in advance

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I'd go for CustomerAttachment, EmployeeAttachment and ProjectAttachment tables with FK's back to the attachment table and the relevant entity...

fritshoutsma SSC Enthusiast Points: 191 More actions · Answer 2

Thanks Howard

That was another option I was thinking about. but it will create serveral new tables. that doesn't have to be a problem, but it seems overdone.

Are there other options to consider?

thanks

HowardW One Orange Chip Points: 29109 More actions · Answer 3

None that I'd consider. I'm not sure why the number of tables would be a deciding factor; normalisation tends to lead to more tables.

You could have 3 nullable FK columns in Attachment with a check constraint to enforce that one and only one of the 3 FK's is set, but you're just making for a more complicated model for no real benefit...

The one design I wouldn't touch with a barge pole is to have a generic refID that's meaning changes dependent on another table.

Lowell SSC Guru Points: 323546 More actions · Answer 4

Anything wrong with having an "AllAttachments" table, and then a multiple linking tables that join the desired tables keys together?

So for example, if i decided to add an attachments functionality to customers. I'd create a new linking table with CustomerId,AttachmentID, which are foregin keys to Customers And AllAttachments.

insert all the attachments into the"AllAttachments" table, and make sure I add the new link to the proper customerID in my new linking table.

then i can repeat that sequence for each section i want to add an attachments table to..Products and Projects in your example would require two new linking tables, joining Products to AllAttachments(ProductId,AttachmentID)

this allows expandability,a s well as having a level of commonality that a business layer can reuse a lot of code, and swap out table names.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Lowell (3/6/2012)
Anything wrong with having an "AllAttachments" table, and then a multiple linking tables that join the desired tables keys together?
So for example, if i decided to add an attachments functionality to customers. I'd create a new linking table with CustomerId,AttachmentID, which are foregin keys to Customers And AllAttachments.

That's exactly what I suggested in the first post, other that renaming the table AllAttachments...:-)

fritshoutsma SSC Enthusiast Points: 191 More actions · Answer 6

I think i'm going to use the extra tabels.

Do you think it's an option to lose the central attachment table and just use CustomerAttachment, EmployeeAttachment and ProjectAttachment tables to save the attachment.

The attachment tables won't have blob files, but just a link to a file location.

Lowell SSC Guru Points: 323546 More actions · Answer 7

something like this?

CREATE TABLE [dbo].[ATTACHMENTS] (

[ATTACHMENTID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

[NAME] VARCHAR(200) NULL,

[EXTENSION] AS (CASE

WHEN CHARINDEX('.',name) > 0

THEN REVERSE(SUBSTRING(REVERSE(name),1,CHARINDEX('.',REVERSE(name))))

ELSE '' END),

[RAWDATA] VARBINARY(max) NULL)

--I can assume this table exists..this is just for clarity/demonstration purposes

CREATE TABLE CUSTOMERS(

[CUSTOMERID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

[NAME] VARCHAR(200) NULL)

CREATE TABLE CUSTOMER_ATTACHMENTS(

[CUSTOMERID] INT REFERENCES [CUSTOMERS] ([CUSTOMERID]),

[ATTACHMENTID] INT REFERENCES [ATTACHMENTS] ([ATTACHMENTID]) )

--I can assume this table exists..this is just for clarity/demonstration purposes

CREATE TABLE PROJECTS(

[PROJECTID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

[NAME] VARCHAR(200) NULL)

CREATE TABLE PROJECT_ATTACHMENTS(

[CUSTOMERID] INT REFERENCES [PROJECTS] ([PROJECTID]),

[ATTACHMENTID] INT REFERENCES [ATTACHMENTS] ([ATTACHMENTID]) )

--etc....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323546 More actions · Answer 8

HowardW (3/6/2012)
That's exactly what I suggested in the first post, other that renaming the table AllAttachments...:-)

doh! i see that now!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HowardW One Orange Chip Points: 29109 More actions · Answer 9

fritshoutsma (3/6/2012)
I think i'm going to use the extra tabels.
Do you think it's an option to lose the central attachment table and just use CustomerAttachment, EmployeeAttachment and ProjectAttachment tables to save the attachment.
The attachment tables won't have blob files, but just a link to a file location.

You could do that. I try to keep things that are the same in the same entity if I can, although it's more of a style than an absolute rule of proper design.

In this case, I'd say that attachments are a common entity and the meaning doesn't change, just what it's related to, so I'd favour sticking with the Attachment(s) table (some people are funny about singular naming conventions) and reflecting the different relationships to it in the design as Lowell and I have suggested.