Database-Designing for status messages

I am building a project in which i make use of status messages

There are two entity groups - Restaurants and Users.

Both can have status messages and both can comment on each other. I am in a fix as to how to get it working with these two entity blocks…

Here goes my try

restaurants (id, name, … )
users (id, name, … )
status_messsage (id, message, timestamp )
status_owner (id, status_message_id, by_type, by_id)
by_type ( id, group_name ) => (1, users), (2, restaurants)

by_id => will be the user id or restaurant_id

i feel this is a little messed up. is there a better solution.

didnt get you on the finite part. can u give an example plz.


Yes. But …

You talked about both groups, restaurants and users, being able to do status messages and comments. Status messages implies a finite set of possible messages whereas comments suggest a different piece of text each time. For comments this new arrangement would be appropriate but for status messages (when there is a finite set) I would put them in a separate table and use the statusmessage_id, as in you original arrangement.

I would lose the by_type and have

status_owner (id, status_message_id, restaurant_id, user_id)

with restaurant_id being null or zero when user_id is non zero
and user_id being null or zero when restaurant_id is non zero

then i can do this right ?
restaurants (id, name, … )
users (id, name, … )
status_messsage (id, message, timestamp, restaurant_id, user_id )

finite = limited number, a distinct list of possible values e.g. (Created, Opened, closed, Deleted)

oops… guess u have mis understood the field… status message is some think like a tweet. or status messge of facebook. :slight_smile: