[SOLVED] Read/Unread comics for each user

[mosi]

I have a script I'm writing which archives comics. I am trying to think of a way to allow users to be able to see which comics they havn't yet read. My script checks various comic sites throughout the day to check for new comics and if there is a new one it will store it. At first I want to make it so it will mark new comics since last visit, then I put a calendar up for each comic which will list each archived comic. I want to be able to make the calendar show up unread comics for the user.

 

the problem I'm having is thinking of the best way to store the read/unread comics. Right now the database has around 5000 archived comics, and it will grow. Do you think it would be alright doing it this way to show all indevidual unread comics (ill provide and option to "mark all comics as read". Or would it be better to show in the comic list "new since last visit".

 

If I did new since last visit and a reader was going through the archive then just left and came back later hoping to continue the reader would have to remember what comic they were at.

 

Does anyone else have any ideas how I can do this?

[cooldude832]

How do you store comics, and do users have to login to view comics?

 

Okay so I see they are database entries does that me they are compressed into mysql as images then squirted back out?

[mosi]

I have a table for each comic called archive_<comic_name>

In that table I have:

 

ID - auto increment

ArchiveID - unixtime of the archived comic, i use this for next / previous buttons under each comic

comicurl - the url to the archived comic

day - used for the calendar

month - used for the calendar

year - used for the calendar

 

As you can guess 1 row per archvied comic strip.

 

I also have another table called comics which stores the ComicID and other info about each comic. This info only changes when a new comic is added (new comic, not archive)

 

Users do not have to login to view the comics no. Right now being regged only gives you access to rating comics, creating a favorites list.

[cooldude832]

This is my idea sine each comic has an ID (not the ArchiveID) you can make a second table in the fashion

 

ID - Table's auto increment

UserID - I'm sure your user table has id this is the same one

ComicID - The Ids off the table <comic_name>

Status - The status of the comic either boolean or a tinyint

 

Now the tricky part is when you first establish this system you will have to import all the old comics and users, and then you will need to import each comic for each new user, but if i'm ready your post right I believe you have a cron job set up to search for new comics, just tact on to that a add rows to this new table feature and set Status = 3 or something or make Status=2 if its unread, but more than X days old and Status to 1 if user read it and you can add more like Status 4 is user favorites, Status 5 is user blocks comic etc etc, only trick is you gotta be updating this because the data gotta be there or just build it on the fly as they login.  Could use ajax and just let the server handle it on login and they don't realize it till they navigate somewhere

[mosi]

If I did this, would it not generate a HUGE amount of data in the database? I have 5000 archived comics (ish) right now, say I have 10 users... that would be 50000 entries. 100 users... 500000 entries. That was the problem I was thinking with my original idea of just having a table with userid, comicid, archiveid. and using REPLACE INTO or something when a comic is read then check with the calendar if the row exists with data userId,ComicId,ArchiveID.

 

So the question really now.. Would that much data in the database be a problem? Every time a new user signs up this would have to add a new table with all the current comic entries in it.

[per1os]

If I did this, would it not generate a HUGE amount of data in the database? I have 5000 archived comics (ish) right now, say I have 10 users... that would be 50000 entries. 100 users... 500000 entries. That was the problem I was thinking with my original idea of just having a table with userid, comicid, archiveid. and using REPLACE INTO or something when a comic is read then check with the calendar if the row exists with data userId,ComicId,ArchiveID.

 

So the question really now.. Would that much data in the database be a problem? Every time a new user signs up this would have to add a new table with all the current comic entries in it.

 

It really shouldn't be as long as you use the INDEX portion for MySQL. It should be fine.

 

Another way to do it is if they do not have an entry for that comic it is "unread" to them. That way you only have data in the db for the "read" ones.

 

EDIT::

On a side note to the "another way" if you wanted the option for "Mark all Read" simple add a date field to the table for the userid, leave the comic id null and if that date field is set any comic created on or before that date is considered "unread" to the user.

[cooldude832]

In the current aspect, no.  MySQL is designed around having millions of billions of entries into it, as long as each row space is minimal

If you understand calculus the idea of mysql is sorta like a integral over a given range (from 0 to $num_rows); 

You can have an astronomical limit of integration so to speak, as long as the individual dAs of each row are not a large amount of data.  Now whats a large amount of data you ask? Well it really depends on your server but in all reality you are talking about <30 bytes per row

ID = Really big numbers, but probably Less than 50 bytes per

UserID = 2-3 digits less than 10 bytes

ComicID = 5-7 digits probably less than 10 bytes

Status = 1 digit so that is 4 bytes

 

74 bytes per row * 50000 rows is about 370kb and mysql can easily handle that

[cooldude832]

frost only issue with that is you only get 1 option this one allows a plethora of options by making Status be a tinyINT (not BOOl) as your case (well psedo BOOL)

[per1os]

frost only issue with that is you only get 1 option this one allows a plethora of options by making Status be a tinyINT (not BOOl) as your case (well psedo BOOL)

 

Yea, I know. Its not very well thought through. Just throwing out ideas, mind you that I would never go to that extent for a system that is that small.

 

My current site houses templates in a database for users plus blogs etc. It is well over 200MBs right now and runs just as smooth as day one. I have about 2,500 users times that by 3. each user, just on the template level, could have upto 15KB of data per template (3 temps for user) which is roughly 112.5MB's just in template data. Not to mention the blog data, comments etc.

 

MySQL is very efficient. You will be fine with that minimal data in the DB. It may seem like a lot, but its not.

[cooldude832]

Good MySQL design is not cramming all the data in a single row, but using lots of rows and properly linking data with ID numbers, also good indexing helps.  The second key step is limiting the number of things you pull on query I see so many people people say select * from Table Where data match and then they end up grabbing a while function 2 out of the 20 fields they pulled.  That is where mysql starts to slow down.  Also understanding INDEX is key

[per1os]

Yea, as long as it is in 3NF form and indexed, everything should run just fine.

[cooldude832]

I don't think there is a max, but if you are making serious $$$ of this and you fear of going over a billion plus entries you might think of looking into oracle for that kind of load, but even with 1000 users and 5000 comics you just hit 5 million and I think mysql is pretty good up to that billion mark

[mosi]

Alright, cheers guys. I'm off home from work now so I'll give it a go later. Was affraid it may be too much data for it to handle but its on a dedicated server atm which is hardly being used so I don't know why I'm worried really. Will post back later with some results if I get chance.

 

thanks for the help. Supprised I have never been here before, think I'll stick around adn try and help out