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?