Need help with Database Design

Question

I have two Models - Users and Restaurants. They can set tweets like status messages and can have replies. i have the following setup and need your help to optimise the design.

users(id, name, ......) restaurants(id, name.....)

modules(id, name) [this table stores the modules - (1,user) and (2,restaurant)

my message table

status_messages(id, pid, message, time, module_id, moduleID)

id - Uid, primary key pid - self ID or parent ID - for accomodating the replies in to the same table. message - the status message is stored here time - the time the status message was set module_id - refers to the module its pointing - Users or Restaruants moduleID - the actual foriegn key

Is this the right way to go about ?

the scale of the application - it would be an open application wordwide. so will one table scale to all those restaurants and users.

1. i wanna have a similar models for reviews which will cater to Restaurants, Dishes, Events, Buffet... on a single table. would it be an over kill.

Answer 1

Yes this design should be fine, you would want to place some kind of index on the status_messages.module_id column so that selects on the table can be filtered by user/restaurant.

Normally on the server side you would create two classes for the status message table

1 class UserMessages 2nd class RestaurantMessages

and use some sort of ORM tool to define the module_id as the decriminator column. So in the database its in one table to maximize reuse. But on the application it has two different names (logical names). Not sure how this works in PHP.