A few design questions

[Koobazaur]

Greetings,

 

I have just registered here and this is my first post, so here goes!

 

I am working on an online content subscription and access method using PHP and MySQL. I have been learning both recently and while I have a decent grasp of the concepts (especially considering I have been programming in C++ for several years now, which I found very similar to PHP), I know little about good coding practices in those areas so I figured I'd ask for some advice instead of just blindly coding away.

 

Basically I will have several online "programs" a registered user can access, where each one would track the users' progress in it. To give you an idea what those are, they would be something like the online personality Test/Quizzes but more verbose (and maybe with a greater level of interactivity). Just as a set up I would need 2 databases:

 

1. A user database

2. A database containing the list of all the programs and associated data

 

 

Now I am wondering how to go about tracking the progress in each program. I was thinking about setting up a new database for each program (which there might be 10-20) which stores the users tha used it and their progress in it. Or would it be a better idea to have one database which would store the Username, Program ID and progress (hence you would have multiple entries of the same user, but different program IDs)? This may not be the best way though considering that each program may track progress differently (one may store only numbers, while another may store strings).

 

On a related on, how do online Forums go about storing the data (which is somewhat of a similar issue) ? Do they create a new database for each thread containing the posts in it, or do they just have two massive Thread and Posts databases and just figure out which posts go with what thread by an ID and arrange them by the date posted (like SELECT * FROM posts WHERE threadID = something) ?

 

Lastly, I was also wondering about the security. I know some basics (like preventing code injections when logging in and whatnot), but know very little about the more advance methods such as SSL and encryption.

 

I know that PHP is server side so the end user doesn't have access to my code. Does that mean I can safely put my database password inside my php files when accessing it? And also, I use POST to transfer login/pass when logging in, which I believe can be intercepted. In such case I should use encryption, correct? How would I go about doing that, though? Should I look into SSL? If so, is there any good website dedicated to these topics that I could check out?

Are there any other issues I should be concerned about that I have missed so far?

 

 

Sorry for the long post and thanks in advance for your answeres!

[Koobazaur]

One more question I forgot to mention:

 

What's the best way to go about keeping track of logged users? Should I just use sessions, or are cookies better for this? Any pros/cons between the two? I have the basic understanding of both, but need to study them a bit more deeply.

[Buyocat]

I won't answer all of your questions, at least right now, but I'll answer the databases one.  Most likely you're using a relational database (a la MySQL) so the trick is to leverage the relationships between the tables.  That means you would create a Users table with a primary key, say, id and then any table which wanted to relate it's content to a user would have a column for named, say, user_id.  You could then constrain the second table such that any value present in the user_id column must be present in the user id column.  Additionally you would constrain the User table such that the id column was unique.  So while some data may be repeated, e.g. first name, in the rows of the User table each row is unique in the sense that it has a unique primary key.  This would let you find all the results you wanted with queries such as:

 

SELECT u.*, p.* FROM Users u, Products p WHERE p.user_id = p.id

 

As for security you'll need to ask yourself what sort of data you're tracking.  SSL is probably not required if you're only taking a user's email/password and perhaps some additional personal information such as name, address, etc.  If you're dealing with credit cards, or you're just extra concerned about then I suppose SSL may be worthwhile, but unfortunately I cannot instruct you because I am now knowledgeable on the topic.

[redbullmarky]

in no particular order, some more possible answers/thoughts:

 

1, a single database with properly thought out tables/relationships will be more than enough to handle a site and all that goes with it. so like buyocat says, joining tables (users/products, etc) via your queries is the way to go.

 

2, i tend to use a database table for tracking sessions, simply because it gives me more control. I can easily pull up a list of all users that are currently on line (like how a forum would) and even log users out if required. regular sessions are stored in files which, whilst it's still possible to do all of this, makes it much trickier. take a look at http://www.php.net/session_set_save_handler for more info on how to implement this one.

 

3, it's perfectly fine to store the DB username/password in your files, but with other things i tend to take extra steps to protect it a bit more as i'm pretty paranoid like that . i tend to be more comfortable moving ANY files that i dont want to ever be made available out of the web root (ie, above the public_html / www / htdocs / httpdocs or whatever your folder is called). PHP's file handling keywords (such as include) can access a file outside the webroot but any requests via the browser cant. This method comes in handy for preventing direct downloading of any uploaded content too via the use of header() and the other file handling functions such as fopen, file_get_contents, etc.

 

hope that helps some

[Koobazaur]

Thanks for the tips. As a clarification, I actually meant TABLES not DATABASES. AS in, I would have a table for each of my programs to track progress of each user.

[Buyocat]

In that case I believe we are in full agreement.  A rule of thumb of to preserve the coherence of a table.  So when you find yourself adding two different types of data to the same table you should probably stop.  In your case each program is really its own entity and deserves its own table.