What would make more sense?

nemesis1931

Hey there php community. Just wanted to say thanks to everyone for their help I really enjoy participating here.

Anyway this is my question:

I want to build a msg system using MySql for different users, would it make more sense to have one table for all messages from all users, or have individual tables of messages for each user?

In terms of space or in terms of speed?

-- I guess Im still trying to decide what is the best way to go about using databases.. you know -- trying to be modular and efficient.. should i use flat files, databases, ... lol

well if you have any idea let me know!

No big o notation please lol.

Thanks !!

devinemke

2 tables: users and messages
relate them together using a primary key field. here's a simple example:

users
user_id INT
name VARCHAR
email VARCHAR

messages
message_id INT
user_id INT
message TEXT or VARCHAR

a basic one-to-many relationship between the users table and the message table.

nemesis1931

🙂
sounds good..

I was thinking about doing this ..

tbl_johnnyfive_msgs

and keeping a log of all messages sent to user johnnyfive on his own table..

as opposed to having all the messages on one table..

any reason why the way you mentioned would be better ?

AstroTeg

Think about database design this way: all like data should be grouped together. Although the data shouldn't repeat (often). If it does repeat, then it probably deserves to go in its own table.

In your situation, having all the messages in one table will work great. They'll probably be pretty unique. But having "johnnyfive" repeated multiple times (for each message sent to him) takes up storage space. So put "johnnyfive" in the user table once, and then associate "johnnyfive"s ID to the message table.

You want to see all messages sent to "johnnyfive"? No problem. Either look up johnnyfive's ID or better yet, join the user and message tables together and just query for "johnnyfive" and the query will bring back all the messages for that user.

I would also mention this - let's say you do make individual tables for all your users (not recommended, but let's say you go this route). What will you do when you realize you need to add an additional column to ALL the user tables? That would suck having to update each and every table with the new column spec. Alternatively, you have 1 table for the messages and one table for the users and if you need to add a column, its no big deal. You add it to the one table that needs it and you're done. So, from a maintenance standpoint, doing the message/user table approach will be easier to maintain and build off of.

nemesis1931

Thanks for the feedback!

heres a question then..

Wouldnt putting johnnyfive's user id in a seperate table and instead referencing it by an id multiple times be similar?

either way somethings repeating...

Hmm alright how about this then?

Would it be faster to look in a table of johnny5's messages which might contain hmm i dunno 100-200 lets just say..

or would it be faster to look in a large message table with 50000 messages just to find the 100 messages that belong to johnny5?

speed or memory isnt that always the question?

johnny5's getting annoying lol lets call the user mary lol.

thanks for your input!!!!!!

AstroTeg

Originally posted by nemesis1931
Thanks for the feedback!

heres a question then..

Wouldnt putting johnnyfive's user id in a seperate table and instead referencing it by an id multiple times be similar?

Yes and no. Here's why:

"johnnyfive" takes up 10 bytes (assuming storage is 1 byte per ASCII character - unicode will make it about 2 to 4 bytes per character depending on the character set - I think).

So for this example, lets say 10 bytes. To store an integer value, lets say its 4 bytes (how MySQL does it).

You store "johnnyfive" once in the user table and that's 10 bytes. Then each time you store a message that references "johnnyfive", that's 4 bytes. Let's say there's 10 messages. That's 40 bytes being used plus the 10 bytes in the user table (or 50 bytes total). If you didn't have a user table but just had a message table, that would be 100 bytes ("johnnyfive" stored 10 times as a string). You probably see the pattern.

Also, when it comes to computing, its a TON easier for a computer to sort a list of numbers then it is to sort a list of names. Databases can build indexes and numeric indexes are a lot easier for the system to work with then an index filled with names.

Originally posted by nemesis1931

Would it be faster to look in a table of johnny5's messages which might contain hmm i dunno 100-200 lets just say..

or would it be faster to look in a large message table with 50000 messages just to find the 100 messages that belong to johnny5?

speed or memory isnt that always the question?

Obviously, 100-200 messages will result in a quicker turn around time. But you'll find even if you have 1,000,000 rows of data, if you properly design your tables (use numeric indexes and normalization where possible) then quering this won't be a big deal. Yes, it'll be a little longer to go through the 50,000, but if you use indexes and a numeric ID to represent "johnnyfive", then the query will still go really fast. Remember, because of indexing, the database can optimize the data in a way which allows the database to quickly find johnnyfive's ID and data.

nemesis1931

great explination. =)

Ill see what i can do with that!!!

Thanks a bunch!!

😃