StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase Modeling: Facebook like messages
text
Body
copied!<p>I'm trying to mimic something similar to FB. Basically, users can post comments in various parts of a user's profile (e.g. "wall", a "photo", etc.). I think the following model will work:</p> <pre><code>=========================== 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 </code></pre> <p><code>parent_id</code> allows messages to be "grouped" into a related discussion. The first message's <code>parent_id</code> will be 0 and subsequent messages will have the PK as the <code>parent_id</code> value (creating a parent-child relationship).</p> <p><code>poster_profile_id</code> identifies who posted the message.</p> <p>The above two tables are very similar. Would it be a good idea to combine them, such as:</p> <pre><code>=========================== message =========================== - id (PK) - parent_id (FK) - type (ENUM: "wall", "media", etc.) - types_id (FK, see explanation below) - poster_profile_id (FK) - message - timestamp </code></pre> <p>In this case, if, say, <code>type</code> is "wall", then <code>types_id</code> is equal to the first table's "wall_owner_profile_id". If, say, <code>type</code> is "media", then <code>types_id</code> is equal to the second table's <code>media_id</code>.</p> <p>I'm a bit concerned that the second approach requires a column to explain the meaning of another column. A disadvantage to this, I suppose, is that there would be no referential integrity for types_id (unlike for "wall_owner_profile_id" and "media_id").</p> <p><strong>What would be the best way to tackle this problem?</strong></p> <p>EDIT 1:</p> <p>Seems like this is the solution so far:</p> <pre><code>=========================== 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) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload