Simple database question

[pianoman993]

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

[Mchl]

Use separate table

 

table message_recipients
messageID, userID
  1,  3
  1,  4
  1,  5
  1,  6

[pianoman993]

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?

[Mchl]

I am. Just create proper indexes.

[BlueSkyIS]

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.

[pianoman993]

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?

[Mchl]

That's what indexes are for. Details are in database storage engine code.

 

 

[pianoman993]

I think that just about covers everything. Thanks everyone for your help

[webref.eu]

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

[Mchl]

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.

 

 

[pianoman993]

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

[Barand]

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]

[Mchl]

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.