3

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?

1 Answer 1

5

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
2
  • Thanks for your reply. Well, the 2 tables approach are independent tied to the CustomerID. Apologise for an unclear question as I am not sure whether should I have 2 functions with 2 tables on 2 different windows interface serving Communication (Message) and File (Uploading and downloading purposes) or should I combine 2 tables into 1 single table 1 function and on 1 window interface (no function seperation).
    – Modular
    Jan 16, 2012 at 19:31
  • If it were me I would still keep function separation behind the scenes, but keep the appearance to the customer that there is no separation. That keeps things simple for them. Go with one window interface that updates whichever table it needs to, based on whether the customer inputs a file, a message, or both.
    – Aaron
    Jan 16, 2012 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.