Combined or separated tables: File and Message?

Is this the best approach to design this table in the purpose of sharing the file online between my customer and I, or should I separate Message table and File table or combined them together?

Combined table approach:
FILE table

• ID

• CustomerID

• FilePath (files'path from both parties: customer and I)

• Content (Message)

• Flag (Read/Unread + ISent/CustomerSent)

• Status (Viewed,Reviewed,Accepted,and etc)

• Timestamp (Current DateTime of file changed)

• CreationDate (Date of file created)

• Dateline (Set the deadline of an action corresponding with the file)

Pros: 1 single table

Cons: 2 actions sharing the same table, in each record, either "FilePath" or "Content" could be null in case customer wants to communicate by writing a message without having attachment (FilePath) or customer uploads any files without having any message (Content) written.

2 tables approach:

FILE table

• ID

• CustomerID

• Name

• Type

• Size

• Data

• Created

Message table

• ID

• CustomerID

• Message

• Attachment

• Created

Pro: No Null field

Con: They are serving the same purpose in these two tables but one focusing on Message and others on File.

What would you recommend?

I would definitely go with two separate tables.


customer wants to communicate by writing a message without having
attachment (FilePath) or customer uploads any files without having any
message (Content) written.

But with the way you're modeling it right now, there really isn't any relationship between file and message, as they both are dependent on Customer_ID. If you tried to query all files and messages for each customer, you wouldn't be able to tell when the two are related (except maybe by looking at the date).

And it sounds like a customer can have a file without a message (and vice versa). I think it makes more sense to create a "Communication" table (for lack of a better word). Essentially it allows you to keep track of all types of communication (message and file) for your customers, but also ties message with file (attachments) when appropriate.

table: Communication
communication_id   int
customer_id        int
created            datetime

That would allow you to further normalize by taking "created" and "customer_id" out of the file and message tables. Likewise, you'd want to add "communication_id" to both File and Message. Then to report on them, you could do something like this:

SELECT *
FROM Customer cu
INNER JOIN Communication co ON cu.customer_id = co.customer_id
LEFT JOIN File f ON f.communication_id = co.communication_id
LEFT JOIN Message m ON m.communication_id = co.communication_id
ORDER BY cu.customer_id, co.communication_id

StackExchange Database Administrators Q#10743, answer score: 5