Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Creating multiple messages in a social media database.

Posted on 2012-08-18
Medium Priority
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
  • 3
  • 2

Accepted Solution

MyMusicWall earned 1500 total points
ID: 38308951
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

ID: 38308971
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

ID: 38309173
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

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

Author Closing Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

564 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