Database Design Question

[jensenat]

Hi, I am in the process of develop a multiuser application. I started today with the database design and I am not sure how to proceed, I have two ideas, but since I am not sure which one will be better, I would like to see what others thinks would be the better approach, or even suggest a different approach if you can think of any other.

Lets suppose I have 1000 users. and each user will need to store information on 9 different places. But each place must be unique for the user (meaning, the user will be the only one writing there) - I am doing this because each user will be storing a lot of information and I am sure I can save a lot of processing time if I avoid the indexing and having to go through thousands of records to recover the records of a single user.

My question is, should I create 1000 databases (one database per user) each with 9 tables, or should I create 9 Databases each with 1000 tables (one table per user)?

I was thinking that maybe the 1000 databases was a better model because I can reduce the number of Database connections, since I can connect once and then send the querries to the different tables on the user database, while the other model needs 9 databases connections to get the same information out of the databases. Anyway I am not sure that is just my opinion, I can't think of any other model for this kind of application but If you do, I am open for suggestions.

[Niheel]

How much information is being stored by each user?

1,000 databases are a lot harder to maintain than 9 databases. That is the first thing that pops in my head.

Maybe you can post a rough schema of what information is stored by each user. Also posts the basic queries you will be doing. Ex. "Look for age and income from all users whose age is over 10".

Get back with us, so we can help.

[jensenat]

Hi KUB365, thanks for your answer. We are in the design process and we dont have queries and all that tecnical details of the application yet. The users will be storing there all kind of information, so is not that we will be storing user information, but the users will be the ones storing data into the databases, the data for each user will be "personal" in a sense that the user itself will be the one providing his own data. For example, data can go from calendar entries (building our own calendar software as part of the application), meeting, apointments, accounts information, personal notes, address book and some extra data that will be produce as part of the company current web services running on the intranet. The thing is that we are providing some generic data templates that the application will be filling from dynamic data, this data is intended for only one user (or maybe a couple of users, but is not for a big comunity). So we were thinking that separating all the data by user was the correct approach. We can have a few databases and separate the data by adding a userid to specify who is the owner of this row, however I dont think that is a smart design due the fact that if I do that I will be searching for a couple hundreds of records in a table with possible millions of records. If I need to do that for about 1000 users (considering this is a small bussines) I will need to go with distributed databases or with big and expensive servers. They dont have the resources to manage all that and I think splitting the info by users will be better performancewise, plus we can use a couple of machines to manage users. I was thinking to use a separate database as a catalog to determine where is the "home database" for each user and from there, we connect to that database and we have everything for the user there on the same database.

I have plenty of programming (c/c++, java, asp, php, perl, jsp), web development (javascript, html, dhtml, xhtml, css, xml) and databases (oracle, db2, mssql, mysql) experience (9 years), but I never faced a design like this one, and it was odd to me, because while we were thinking on the design, I though that having close to 1000 databases was a crazy thing, but now I am starting to convince myself that maybe that is the best approach, however I am still accepting suggestions, since I am not behind in schedule yet and I want to have the best design as possible by the time we start writing code.

[Niheel]

Well maybe you are right. I have dealt with a company that has used the seperate database for each client approach. Scaling is lot easier, as you can just add more machines. The approach does work.

Things to consider should include;

• deployment of codebase across multiple machines
• deployment of changes to all the databases and machines
• a master client database that keeps track of each users database password/username general stats - etc.

Well I think you have the right approach. If you feel in your gut this is the way to do it; you should attack the design of it on paper.

With any application design, forethought will save you a lot of time, resources and money in the future.

Keep us updated.

~KUB

[jensenat]

Thanks KUB. Just to add something else, I dont need deployment of source code accross different machines, we are going to use a current centralized database that will allow us to authenticate users They have this up and running and we are just adding a way to identify the server that has the database with all the new user data. The application server will have an interface to the database servers (for security purposes) so the communication between the database server and the application server will be through a web service (using an XML protocol) instead of a direct communication through ODBC, JDBC etc etc. The design is complex, but everything is clear, at least I think and hope so, there are still things that are not complete, but I think we are on the right path.

Two of the most importan requirements (I think this is a general requirement almost everywhere) is to have the less possible down time and an acceptable response time. So I have one server for the central catalog, and three servers for the databases. All these have replication schemas and backups just in case and to guarantee service availability, also I am planning to provide an extra server that will serve as the "six man" here, so if any of the servers goes down this extra server will assume that possition until the admins take care of whatever the error is. Like I said there is more behind all these, but I just wanted to give you a better idea what we are trying to do. Thanks for your help and like I said before, I am still open for suggestions if someone else wants to contribute with something.

[taylan]

Hi Jen,
Thank you again for your previous response (this is a follow up for my thread,"User Authentication"), I tried to put your suggestion in the following script of mine but didnt work. As soon as it authenticates the user, it still stays at the authentication page instead of directing to a new page. Can you see whats wrong below? I basically want this authentication.php page to verify user_id and password and then if the user passes, then direct the user to another page.

Thank you again. Sorry for posting here but I was trying to find a way to reach you.

$request = "SELECT * FROM users WHERE password='".$pass."' AND username='".$user."'";

//Pass the request to the mysql connection,
//and the data is returned in a blob and
//saved in $results
$results = mysql_query($request,$conn);


//if mysql returns any number of rows great than 0, we know we had a match,
//right?
if(mysql_num_rows($results))//function returns true if any matches are found
{
$_SESSION['user'] = $user;
$_SESSION['auth'] = true;
$url="http://myserver.com/redirected.php";

}
else
{
echo "User Name/Password Pair Not Found.";
$_SESSION['auth'] = false;
}


//End PHP file authenticate.php
?>