I am attempting to mimic something such as Facebook. Essentially, customers can publish comments in a variety of areas of a user's profile (e.g. "wall", a "photo", etc.). I believe the next model works:
=========================== wall_message =========================== - id (PK) - parent_id (FK) - wall_owner_profile_id (FK, identify whose wall the message is for) - poster_profile_id (FK) - message - timestamp =========================== media_message =========================== - id (PK) - parent_id (FK) - media_id (FK, identify which photo, video, etc.) - poster_profile_id (FK) - message - timestamp
parent_id enables messages to become "arranged" right into a related discussion. The very first message's
parent_id is going to be and subsequent messages may have the PK because the
parent_id value (developing a parent-child relationship).
poster_profile_id identifies who published the content.
The above mentioned two tables are extremely similar. Will it be smart to mix them, for example:
=========================== message =========================== - id (PK) - parent_id (FK) - type (ENUM: "wall", "media", etc.) - types_id (FK, see explanation below) - poster_profile_id (FK) - message - timestamp
Within this situation, if, say,
type is "wall", then
types_id is equivalent to the very first table's "wall_owner_profile_id". If, say,
type is "media", then
types_id is equivalent to the 2nd table's
I am a bit concerned the second approach takes a column to describe this is of some other column. An obstacle for this, I guess, is the fact that there'd be no referential integrity for types_id (unlike for "wall_owner_profile_id" and "media_id").
What can be the easiest method to tackle this issue?
Appears like this is actually the solution to date:
=========================== message =========================== - message_id (PK) - parent_message_id (FK) - profile_id (FK, referring to who posted the message) - message - subject (applicable only for emails) - timestamp =========================== wall_message =========================== - message_id (FK) - profile_id (FK, referring to who received the message/owner of wall) =========================== media_message =========================== - message_id (FK) - media_id (FK) =========================== email_message =========================== - message_id (FK) - profile_id (FK, referring to who received the message)
You might have your table message, after which n:m relationship tables, i.e.
message_to_wall: - messageID - wallID message_to_media: - messageID - mediaID
By doing this you retain the referential integrity and just have one message table.
This obviously would technically let it possess a message published to some wall And also to a media item (photo, etc.). Which means you can't easily restrict this.
Otherwise - if you don't really need a relational database, you can consider utilizing a NoSQL database like CouchDB or MongoDB. You are able to store all individuals comments directly on the wall or media document. This way you do not have all individuals needed JOIN queries and also the surveys are all from the media or wall.