Database Modeling: Facebook like messages

Question

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:

===========================
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 allows messages to be "grouped" into a related discussion. The first message's parent_id will be 0 and subsequent messages will have the PK as the parent_id value (creating a parent-child relationship).

poster_profile_id identifies who posted the message.

The above two tables are very similar. Would it be a good idea to combine them, such as:

===========================
message
===========================
- id (PK)
- parent_id (FK)
- type (ENUM: "wall", "media", etc.)
- types_id (FK, see explanation below)
- poster_profile_id (FK)
- message
- timestamp

In this case, if, say, type is "wall", then types_id is equal to the first table's "wall_owner_profile_id". If, say, type is "media", then types_id is equal to the second table's media_id.

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").

What would be the best way to tackle this problem?

EDIT 1:

Seems like this is the solution so far:

===========================
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)

Answer 1

First, a few responses to small points, to keep you on the straight and narrow path of Relational databases and db design.

1. The whole idea is to place as many of the Rules right in the database, in one place, and not in code. Almost everything can be done via DDL: FK constraints; CHECK constraints; and RULES (all ISO/IEC/ANSI SQL requirements). Then all the users (your app is an user) can see all the rules and understand the database better. That protects the db, no matter what client is used to execute the code. Db vendors (that means commercial, not freeware) implementation of these constraints are more reliable than code.

2. The requirement (not convention) for inserting rows to a child table is that the parent row must exist first. That is what the FK constraint does, it ensures the parent row exists. In a many-to-many table, both parent rows must exist before the child (with two FKs, one to each parent) can be inserted.

3. types_id is a horrible idea because you have broken design rules, and removed the possiblity of RI. Better to have separate columns with RI (FK constraints to each parent). (But there is an even better way.)

4. All yourId columns, the PKs, should be renamed TableId. Each should have Private DataType of the same name. The column name is used unchanged wherever it exists, as an FK. The only exception is where you have two FKs to the same parent table: there it should be RoleTableId.

What would be the best way to tackle this problem?

Normalise. And you will have issues that are exposed, which you need to resolve. Therefore Normalise again. And keep doing that until you have no issues to resolve.

1. Your single Message table is already half way there. You have intuitively Normalised the two tables into one. But there are issues to resolve, so let's handle them.

   • Sebastian has provided the two many-to-many tables, so I won't repeat.
     .

2. Before you decide that that is final (and therefore the two many-to-many tables are final), I suggest you Normalise Wall and Media. To me, it looks like there are many common columns. If you Normalise that, you will get one table. Since it is a Thing that is exposed or furnished by a Person for the purpose of inviting Messages, and the type can be{ Photo | Album | Mailbox | Wall }, I would call it PersonFurniture or PersonObject.

   • If that ends up as one table, then you won't need two many-to-many tables, just one.

Responses to Comments

1. It is easier and faster to draw the model, than to type long discussions. I have thought about most of your questions. Please check this and ask specific questions about anything you do not understand.

Link to Social Network Data Model (Page 3)

Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.

• CHoose your own table and column names
• Message.Subject can be set to CHAR(0) or ignored, if it is not Email.
• that wall_message and email_message are identical is not a problem, I've Normalised them into one table
• whether it is a wall_message or email_message or media_message is a matter of where it is "sent", right ? You can easily disallow any function (eg. grouping) for any message type via a CHECK constraint.
• you haven't answered (2) above
• I think message grouping is different from media grouping: think about when a photo album has a list of messages on it.
• nothing is a problem, the whole idea of modelling is, paper is cheap; the whole idea of Relational dbs is, to do as much as possible using constraints, checks, rules. If anything is wrong we can change it.

(Do you want Race (3 levels) or 2 levels in your Ethnicity question ?)

Answer 2

You could have your table message, and then n:m relationship tables, i.e.

message_to_wall:
- messageID
- wallID

message_to_media:
- messageID
- mediaID

This way you keep the referential integrity and only have one message table.

This of course would technically allow it to have a message posted to a wall AND to a media item (photo, etc.). So you can't easily restrict this.

Otherwise - if you do not really require a relational database, you could think about using a NoSQL database like CouchDB or MongoDB. You can store all those comments right on the wall or media document. That way you don't have all those required JOIN queries and the comments are all linked to the media or wall.