pianoman993 Posted August 27, 2008 Share Posted August 27, 2008 Hello, I am creating a private messaging system and I am having trouble figuring out how to allow for a person to send a message to multiple people. Here's an example of what I mean. If I sent a message (I have an ID of lets say 3) and I would like to send my message to a group of people (IDS of 4, 5 and 6), I would insert the numbers 3,4,5,6 into a recipient field and 3 into an author_id field. Is that a good method for allowing multiple recipients? I ask this because it defies the rules of normalization. Also, if this is the correct method, then how do I insert multiple values into a database and have them seperated by something like a ; and how do i search the database to find if a number in the array exists? Any help would be greatly appreciated! Thanks! - Mark Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/ Share on other sites More sharing options...
Mchl Posted August 27, 2008 Share Posted August 27, 2008 Use separate table table message_recipients messageID, userID 1, 3 1, 4 1, 5 1, 6 Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626870 Share on other sites More sharing options...
pianoman993 Posted August 27, 2008 Author Share Posted August 27, 2008 That makes sense, are you sure that it if there are something 1000 messages, that this won't waist space in the database and be too hard to search? Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626873 Share on other sites More sharing options...
Mchl Posted August 27, 2008 Share Posted August 27, 2008 I am. Just create proper indexes. Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626880 Share on other sites More sharing options...
BlueSkyIS Posted August 27, 2008 Share Posted August 27, 2008 it has to take space somewhere, the more efficiently the better. it won't "waste" any space until you're hitting billions of records. no worries. Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626881 Share on other sites More sharing options...
pianoman993 Posted August 27, 2008 Author Share Posted August 27, 2008 Gotcha, and sorry for the nooby follow-up question but I just have to ask, how can script search a database with say, billions of records, and still return a result in a matter of seconds? Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626883 Share on other sites More sharing options...
Mchl Posted August 27, 2008 Share Posted August 27, 2008 That's what indexes are for. Details are in database storage engine code. Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626887 Share on other sites More sharing options...
pianoman993 Posted August 27, 2008 Author Share Posted August 27, 2008 I think that just about covers everything. Thanks everyone for your help Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626888 Share on other sites More sharing options...
webref.eu Posted August 27, 2008 Share Posted August 27, 2008 As Mchl has said, just make sure your tables are properly indexed. I think it helps to have a unique ID autonumbering column in each of your tables which you can index on. If you have designed your relational database properly, i.e. fully relational, linking tables via row ID numbers, searches involving large numbers of records should be performed reasonably quickly. Rgds Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626889 Share on other sites More sharing options...
Mchl Posted August 27, 2008 Share Posted August 27, 2008 I use autoincrementing IDs a lot, but they're not always the best solution. In the table I posted above, it'd be completely useless. Primary key should be on messageID,userID. Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-626895 Share on other sites More sharing options...
pianoman993 Posted August 27, 2008 Author Share Posted August 27, 2008 Sorry, I just have one more question that just crept into my head and that is, how do forums ( like this one for instance) make it in their database so that the user ( me ) knows what thread was viewed since it was last modified and which thread was not. It would be a complete waist for the database to make a table containing a row for each user correlating to each thread in the forum so how do they do it?! I'm quite puzzled on this Any ideas? - Mark Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-627203 Share on other sites More sharing options...
Barand Posted August 28, 2008 Share Posted August 28, 2008 I'd expect to see something like this in the DB [pre] thread thread_view user ---------- ------------- ------------- threadID ---+ viewID +---- userID date_posted | userID ----+ profile_stuff etc +-- threadID view_time [/pre] Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-627388 Share on other sites More sharing options...
Mchl Posted August 28, 2008 Share Posted August 28, 2008 For SMF you're not far away There's [pre] table (prefix)_log_topics ID_MEMBER ID_TOPIC ID_MSG [/pre] ID_MEMBER indicates forum user of course ID_TOPIC indicates a topic ID_MSG indicates last message in topic that user has read Or so I understand from quick examination of database structure and forum code. Link to comment https://forums.phpfreaks.com/topic/121552-simple-database-question/#findComment-627394 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.