Creating multiple messages in a social media database.

Posted on 2012-08-18
Last Modified: 2012-08-23
hi, so far i have created a user table, messages table, friends table and other tables which are not needed for this section of my project.  my messages table currently reads as such. the fields are in order: message_id, owner_id, subject, body, date_time_posted, is_public.

1	1	Party at my place	2nite 7 pm till l8!!!! CU there!!!	                2011-02-15 15:06:28     1	
2	1	COMPSCI280	        Just enrolled there, seems a cool course	       2011-02-15 15:20:35	   1
3	1	Hangover!	        Maaaan, that party rocked!	                       2011-02-16 11:45:15	   0

Open in new window

so basically my current table has messages, and can distinguish between whether the message is public or only visible to the users friend. i now want to make a third type of message, which is only visible to selected friends (i.e. not all).

so i think i have to make a change to my messages table so it can now distinguisgh between the 3 types of messages. and also create a new table which will work as message membership table. how would i go about doing these?
Question by:Vishesh1990

    Accepted Solution

    Hi,  I did something similar on an Intranet site.  

    Firstly for the private messages you will need a field to record the recipient_ID otherwise you will have no way of knowing who it's for.  

    Entering a value of say 2 as the is_Public field value would indicate it's for a group of people.  To store who the message is for you could setup another table with who the message is for,

    Something like tablename=group_message_ID with the following fields.  Message_ID, Sender_ID & Recipient_ID so multiple entries in this would allow for what you are trying to do.

    However something to consider, what happens when one of the group replies? If for example there were 10 people in the original then 10 records would be written each time a reply to the group message was done.  To get around this I added another field to the main table called thread_ID, then changed the field in the new table to relate to the thread_ID instead of the Message_ID

    I hope this helps

    Author Comment

    hi thanks for your response. i was thinking the exact same thing, but thought that because my message table already includes owners_id. do we have to also put Sender_id in the new table. because when we link the message_id to receipent_id. we are essentially already linking the owners_id. so no need to repeat this.

    the only reason im saying this is because i am trying to make this as small and efficient as possible.

    i see what you say about thread_id. because all responses will come under one thread we will not be getting massive repeats. (again saving storage space).

    and also simply making the new value '1' or '2' is sufficient enough to distinguish between the 3 types? no more changes needed?

    Expert Comment

    You are correct, you would only need it in the message table..

    No you shouldn't need any other changes to the database schema :-)

    What are you developing the Social Media site in?  I'm writing one using ASP

    Author Comment

    thanks for that, im running mine on a phpMyAdmin server run at another location.

    Author Closing Comment

    thanks for the tips, it got me heading in the right direction. thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now