Need help with Database Design

Databases
1

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.
2

okay, i think what you’re looking for is a supertype/subtype structure

google that for more information

your messages table would reference the supertype (user/member/entity/thing) table, while users and restaurants would be two subtypes

3

I see what your doing.

Your having the messaging system be compatible with all modules.

Though can’t the module moduleID be deduced by the user who created the message or may a user exist in multiple modules? If a user can exist in multiple modules than what you have looks to be the way to approach it.

4

Its the same. am sorry i use cakephp framework and used to calling it model. its module itself

it has module_id which referes to the modules table which stores users and restaurants.

basically when module_id = 1 its a user… when its 2 its a restaurants

ModuleID referes to the actual foreign key based on its users or restaurants.

5

they are not sending any message. its more of a tweet. a restaurant can tweet about the news or the offers they have that day or annouce something to its followers.

6

that wasn’t a very good example of a restaurant sending a message

7

and you want restaurants to be able to send a message? that doesn’t sound right

8

Users can set status messages. and other users can reply to it.

Restaruants can also set status messages and users can reply to it.

something like that.

and i am also storing the reply in the same table using pid.
pid will be selfid if its a status message and not a reply
pid will be parent id if its a reply to some other status message.

hope this helps. am sorry i havent been able to convey it right.

9

what is the difference between a model and a module?

and why do you have module_id as well as moduleID in the same table?