Database Structure for MessageBoardApp

Question

Ok i was told my last question was too wide so i try it more precise this time.

I need a Database Structure for a MessageBoard App. I have 2 ideas but dont know which scales better. Is there another possibility that is even better than my ideas?

There are Users, Messages and Groups. All messages belong into at least one group but can be in more. Users subsscribe to at least one Group and than can see all Messages of all Groups they belong to. Users can create Groups at will.

Scale(theoretically): Users: Millions Messages: Billions Groups: More than Users

I have two ideas right now:

Idea 1:

Table Users:

• ID
• All personal information...
• GroupsSubscribed (string with all group IDs?)
• LastUpdate (Date)

Table Messages:

• ID
• ImageURI
• Text
• Creator
• Date
• Groups
• Answer_Messages_IDs (String with all IDs of messages that are an answer to this message)

Table Groups:

• ID
• GroupName
• LastUpdate (Date)

Idea:

Message Get:

1. App gets every X seconds the Group-LastUpdate (DB call: Group)
2. If Group-LastUpdate > User-LastUpdate -> Select all Messages where Groups contain Group and Date > LastUpdate (DB call: Messages)

Message Write:

1. App writes Message belonging to more Groups
2. Save Message in Message Table (DB call)
3. Update Group Table LastUpdate (DB call)

-----------------

Idea 2:

Table Users:

• ID
• All personal information...
• GroupsSubscribed (string with all group IDs?)
• NewMessages (string with MessageIDs?)

Table Messages:

• ID
• ImageURI
• Text
• Creator
• Date
• Groups
• Answer_Messages_IDs (String with all IDs of messages that are an answer to this message)

Table Groups:

• ID
• GroupName
• UserIDs (string with all user IDs)

Idea:

Message Get:

1. App gets every X seconds the User-NewMessages(DB call: Users)
2. If User-NewMessages != "" -> Select all Messages where ID in List of NewMessages (DB call: Messages)

Message Write:

1. App writes Message belonging to more Groups
2. Save Message in Message Table (DB call: Messages)
3. Get Groups-UserIDs for every Group (DB call: Groups)
4. Update every User with new Message ID (DB call: Users)

Answer 1

This is an exercise in database normalization as @Paul Spiegel indicates above.

You would create something like the following:

Users

• UserID PK
• ImageURI
• ... personal user informational columns ...

Messages

• MessageID PK
• Text
• UserID FK -> Users(UserID) // Message Author (Creator)
• Date

Replies

• MessageID FK -> Messages(MessageID)
• ReplyID FK -> Messages(MessageID)
• PK (MessageID, ReplyID)

Groups

• GroupID PK
• Name
• Description
• UserID FK -> Users(UserID) // Group Moderator -- I'm just adding this one in for fun.

User_Groups

• UserID FK -> Users(UserID)
• GroupID FK -> Groups(GroupID)
• PK (UserID, GroupID)

Message_Groups

• MessageID FK -> Messages(MessageID)
• GroupID FK -> Groups(GroupID)
• PK (MessageID, GroupID)

I moved ImageID from Messages to Users on the assumption that it is a user Avatar. If it is really something else associated with a message, then move it back.

There are three application integrity rules in addition to the PKs and FKs already included.

AIR #1 - The existence of a row in Messages implies at least one matching row in Message_Groups.

AIR #2 - The existence of a row in Users implies at least one matching row in User_Groups.

AIR #3 - A given ReplyID can only appear once in Replies. This keeps adjacency list semantics preventing a generalized many-to-many association and enforcing a hierarchical association.

The database join logic and application code is left as an exercise to the reader.