[web] Database Design

Creating a seperate database for each user wouldn't be feasable. Databases are ment to handle tables with lots of records. Even with record counts in the millions you should not have performance issues if you are utilizing the database properly; avoiding outer joins or anything that requires a full table scan. With indexed tables, the database will be able to point almost directly at the record without the need to scan through the entire table.

As far as the structure of the database, it's best to start out completely normalized. If you find specific areas that are causing a performance bottle neck, restructure accordingly. Typically its easier to move from normalized to non-normalized, rather than the othe way around.

ok quick question about indexes. What is the difference between using an index and then just having a table using interger primary keys. I heard indexes are seperate but if your scanning for information on an index what is the difference in just scanning for the info in the orginal database.

Not much, because a primary key is also an index. Indexes help the computer speed up queries. If there is no index then the computer must look at every row in a table to see it it matches your query. Indexex allow it to skip many records. There can be only one primary index per table, but you can have more than one index, and an index can cover multiple comumns. You should have an index for every column you join on and every column you sort on.

So indexes will only speed up queries if you have several tables and your joining it together. For something as simples as having an index for a login setup there isnt really a point of an index because it is still forced to look in the entire table for the matching record. But from that if you want to link the user and then retreive his comments it will speed things up since all you have to do is match the I believe its called the foreign key on the other table so you know these comments belong to the specific user. So indexes are only useful when your querying something and you are joining several tables together. Am I right?

And my final question is how do a database backend machine work? You need several web servers for a website to handle all the request but when these websites interact with database how do you handle all the reqest? I first though you can have several computers with the database on it. But the problem would be that some databases have updated information and then another database will have another set of updated information. So how would I interact with a database that could have possibly 10,000 request in as little as a minute and still be fast and efficent and run off of one database but possible running it from more then one machine.

Quote:

So indexes will only speed up queries if you have several tables and your joining it together. For something as simples as having an index for a login setup there isnt really a point of an index because it is still forced to look in the entire table for the matching record.

In theory you could put an index on the username and password columns. But a user will only login once, so there's no point in optimizing this.

Quote:

But from that if you want to link the user and then retreive his comments it will speed things up since all you have to do is match the I believe its called the foreign key on the other table so you know these comments belong to the specific user.

Yep. An oversimplified example:

* user logs in and user's (unique primary) id is stored in the session *
SELECT id FROM tblUser WHERE username = [username] AND password = [password]
* select user's comments, fk_user is a foreign key in tblComment refering to tblUser *
SELECT * FROM tblComment WHERE fk_user = [id]

Quote:

So indexes are only useful when your querying something and you are joining several tables together. Am I right?

Pretty much. In general it speeds up the search for records. The query itself doesn't necessarily have a join in it. It may also speed up sorting (especially with a clustered index).
Keep in mind though, that indexes can slow down updates and insertion of records.

Quote:

And my final question is how do a database backend machine work? You need several web servers for a website to handle all the request but when these websites interact with database how do you handle all the reqest? I first though you can have several computers with the database on it. But the problem would be that some databases have updated information and then another database will have another set of updated information. So how would I interact with a database that could have possibly 10,000 request in as little as a minute and still be fast and efficent and run off of one database but possible running it from more then one machine.

If you have one database server and multiple web servers (load balancing), the web servers just connect to the one database server. A web server may cache certain data locally if desired and possible.
If you have more than one database server, you either have to split up the data (is not always possible) or use a merge replication to keep the data in sync. But I'm not sure if the latter is possible using MySQL, you may need an engine like Microsoft SQL Server.

Quote:

Original post by WanMaster If you have more than one database server, you either have to split up the data (is not always possible) or use a merge replication to keep the data in sync. But I'm not sure if the latter is possible using MySQL, you may need an engine like Microsoft SQL Server.

I think that's what row based replication is all about.

Anyway, the best way to go about it is to simply build the website on a single machine and then stress-test it with profiling on to see where you can get the most bang for your buck. If there's lots of serverside scripting going on then you could consider splitting up the webserver and the database server, or use multiple webservers. Alternatively you could minimize the dynamic contents on the website and put a squid server (cache proxy) in front of the webserver. Splitting the database server should be done when the SQL, not the server side scripting is the bottleneck.

Profiling, profiling, profiling.