proper database structure

safra

Hi,

I am making something quite smilar to a website forum: you can subscribe as a user and you can post messages.

What I had like to do add is an "do not show this post again" option for users regarding individual posts. First thoughts, add a new table "IgnorePosts" with columns ID, threadID, UserID.

But let's be optimistic and consider a couple of million users and thousands of posts. This "IgnorePosts" table could become massive.

Is there a better way to handle this?

Thanks!

johanafm

Yes it could, but that table would still have foreign keys on both user_id REFERENCES user(id) and post_id REFERENCES post(id). Both post and user would have primary keys on their respective id columns meaning that you have all the indices you need.

Moreover, it's possible that one database server would no longer be able to handle the load, but with millions of users, you should be able to afford several db servers.

dalecosp

How many posts would a typical user ignore? Could you make one entry for each user and an array of "ignored" post numbers?

(Quid pro quo: my DB-fu is pretty sucky 😉 )

safra

@, Yes, I don't think the indexes is the problem, I just wonder about the number of records and time to process...

@, A single record per user could be an option and I understand the idea. But what exactly do you mean with an "array" of ignored post numbers. Can arrays be stored and accessed in a mysql database?

Derokorian

Indeed see serialize()

safra

Ok, but it seems you will have to search for all active messages in PHP, you cannot get them directly from the MySQL table?

Derokorian

Yeah, I was just linking a way to put an array into MySQL heh. TBH the best solution IMO is an ignored_posts table with 2 columns (possibly 3 if you want to know WHEN they ignored it) user_id post_id [datetime*] I don't feel a unique id is necessary for this table, as the user_id/post_id combo will be unique on its own. This is a standard many-to-many relationship table

*optional

Ashley_Sheridan

I wouldn't worry too much about the number of rows in a particular table. With the right indexes everything should be fine. I've worked on plenty of sites that had tables with hundreds of thousands of rows, sometimes millions. The key is getting your indexes right, and not querying unecessarily, such as getting the ignore posts list for every page on a site, even the pages where you show no posts.

The only time I've seen problems with DB's that contain millions of rows is where there was also a lot of data in those rows. If you need to do that, then consider separating the data a bit into multiple tables.

johanafm

Definitely don't put one row per user containing all ignored posts. You break database normalization which leads to bad things. It should be one row per user and post.

safra

johanafm;10996113 wrote:
Definitely don't put one row per user containing all ignored posts. You break database normalization which leads to bad things. It should be one row per user and post.

Is that a definite no go?

1 million users, 50 threads

option 1: a single row per thread:
result: 50.000.000 rows

option 2: all active threads serialized by id in 1 column in a single row
result: 1.000.000 rows

What bad things can happen when chosing for option 2? I know it is not proper database design but given the fact that threads will never be phisically deleted from the table, would this still be a bad idea?

Is there a limit on the number of rows in a MySQL table? I read something like 5,000,000,000 rows. Is that a realistic number or just "technically" feasible?

Derokorian

MySQL's only size limitation is that of the OS its running on, for example if you have it installed on windows with a fat32 file system the tables can't grow beyond 4GB.

http://dev.mysql.com/doc/refman/5.0/en/full-table.html
http://dev.mysql.com/doc/refman/5.0/en/table-size-limit.html

As you can see from the links, a MySQL server on solaris can get as big as 16TB! Therefore MySQL itself doesn't have size limitations but the hardware and software running it do.

Weedpacket

safra wrote:
Is there a limit on the number of rows in a MySQL table? I read something like 5,000,000,000 rows. Is that a realistic number or just "technically" feasible?

It's perfectly feasible; but circumstances could get in the way. If you have a unique integer key you'll want to use BIGINT instead of just INT, otherwise you'll suddenly find the table is effectively "full" and refusing to insert more rows after the 2,147,483,647th (or twice that if you thought to use an UNSIGNED INT). Search for TINYINT Duplicate entry for examples of people running into this problem when they picked a column type that was too small.

Derokorian wrote:
MySQL's only size limitation is that of the OS its running on, for example if you have it installed on windows with a fat32 file system the tables can't grow beyond 4GB.

That's not the OS's fault - why not just use more than one file per table?

johanafm

Weedpacket;10996234 wrote:
That's not the OS's fault - why not just use more than one file per table?

While the DBMS certainly could be able to deal with this on its own, perhaps at this point it would make sense for user defined paritioning anyway.

Weedpacket

I was actually thinking in the opposite direction: user-defined partitioning, with the DBMS stepping in when that's not enough (since the former can result in unpredictable file sizes depending on partitioning rules/table content).