is one db enough

one vs more

[ag_47]

Hi,

I am working on a new website, and I'm kind of new to DBs and such. (not html and web design in general).

In the website, people will be able to post stuff in different categories (which in turn have sub-categories).
I'm thinking of having a main database for global stuff, and other dbs for each category to store posts in this category and its sub-categories. The number of categories can grow 200+.

Is this a good idea? My guess is a single database won't handle everything by itself, so it's more efficient to divide the load - or am I only going to create extra loading time with the secondary dbs?

In other words, how efficient is one db v.s. one global and 200 other.
Note: Users won't be jumping around categories much, and will mainly stick to theirs.

[LifeinAsia]

Do you mean databases or tables? Which DB are you using?

Without additional information, I would say that everything in one DB makes the most sense and will require the least amount of maintenence.

[ag_47]

Sorry for the confusion. By database I mean a collection of tables. For example the main db will have various tables like user info, page templates, and other global info. The secondary dbs will contain tables specific to each category.

I'd all of them will have about 5-10 tables (with plenty of data). It will start off small - but there's a good chance I'll ad more and more.
Oh, and im talking mysql

[Johan007]

I am not much of programmer but I am convinced you still have to learn the basics.

You must not create tables with duplicate fields. Inside the table you can have as many records as you like mySQL (unlike Access websites) will handle it.

To understand the paragraph above you must know what these are:

Database
Tables
Fields (You must never have repetition of fields in you database)
Records (You must avoid duplicate content in Records)
Relationships between tables (Why link database tables using SQL in your programming)

Now your ready to build your db.

[edited by: Johan007 at 8:22 am (utc) on April 23, 2008]

[ag_47]

Well, I think I know the basics - I certainly know what you're talking about.

Maybe I should rephrase myself. If a website uses a single database, then every time someone visits different pages of this site, they will keep requesting connections to this single database. And as more and more users visit the website, all these connections and queries slow the server down.
The website I'm working on contains different categories dividing the traffic between them. So instead of having a single database to handle everything, I want to create many databases for each category - which in turn should divide the amount of queries to each database. Or will it? This is what I am trying to make sure. After all, there is no limit on the amount of databases you can have right? So if I have more than one handling different groups of users, I hope this will increase the server's efficiency.

Thnaks.

[elitebomber]

More databases does not equal more efficiency. Your databases are mosty likely going to reside on the same server unless you spread them out on multiple servers. Since the traffic coming in and out of your server is the bottle neck, the fact that all requests are going to be using one database will increase efficiency by taking advantage of temporal/spacial locality of the server's cache.

If two tables are related in any manner you are going to want them in the same database.

[Johan007]

elitebomber bomber is absolutely correct if it is on the same server then one mySQL db is most efficient. If you are getting a slow down look into caching.

On a related note it would be good if some of you could take a look at my related load issue here: [webmasterworld.com...]

[edited by: Johan007 at 7:50 am (utc) on April 24, 2008]

[topr8]

i'd have one mySQL database.

the size of the database is not that important - it is the number of simultaneous connections.

when this becomes a problem - you can look into clustering database servers.

[ag_47]

Thanks, guys, for the replys - thats exactly what i was looking for. I'm glad that got clarified before I got to any programming (I'm still working on the design :P).

But unfortunately, now I have to think a little harder about how I'm going to organize all the data. Each category was going to have similar data, in similar tables divided between databases. Clearly I need to combine these.
And I guess when it starts out and few people use the website, everything will be fine but, as with any server, if everything goes smoothly I expect to have big increase in the amount of data.

So when I start, with fewer posts in fewer categories, I plan to either rent - or probably host myself, a single server handling all data in a single database. Then when time comes to move up, I want to be able to easily upgrade to multiple servers, etc. Does this seem reasonable, or do I need to consider a lot more around this? How do people go about doing this? (I'll Google around, clustering seems reasonable, but given my situation/plan with the whole category deals, any advice? Anything else I need to know? I don't want to rush with anything, and make sure I understand where im going. I got the idea, I want to make sure it turns out good)

Thanks again

[LifeinAsia]

Unless you're getting tens of thousands of page views/hour, it's doubtful you'd need to worry about clustering. If you're having problems before that level, focus your efforts on indexing your tables as well as optimizing your code.

[ag_47]

All right, I'll go back to my plans and reorganize everything into a single database. I'll try my best to make everything efficient, and have the ability to update things with ease.

[rienarry]

I read all replies and they are very useful. I remind one thing in desktop application development that a connection can be create once and used across the application without needed to reinstantiate the connection object (CMIIW). I haven't tried to use it in web application but It seem it has the same concept (with no take a deep look into the security issues). I'm still thinking if it's able to be stored in a session so each visitor will just create one connection until the session is ended. any opinion?

[ag_47]

You mean like having a single connection for each user and transferring it between pages?
This probably won't work, but what if the connection link variable is posted to another page? Will it still work?

You can also try mysql_pconnect();
[ca.php.net...]

[phranque]

if you are using apache and perl you can use mod_perl and Apache::DBI [search.cpan.org] to create a persistent connection.

if you are using php, you might want to read this:
[php.net...]

[Lts95]

Then when time comes to move up, I want to be able to easily upgrade to multiple servers, etc. Does this seem reasonable, or do I need to consider a lot more around this?

The suggestions on optimizing the database site first with mod_perl/apache::dbi or the php equivalent are good. However, it sounds like you want to do all of this yourself. In between all this technical server management you'll also need to work on SEO and SEM to reach the lofty traffic goals you are setting for yourself. That's a lot of work for a beginner to database driven web sites. It took probably 6 months (part time) development time to move from perl scripts hitting a flat file db to mod_perl using MySQL, and there were more than a few hiccups in between.

I think your best option to start with is to find a web host that offers database driven web sites. I would imagine there are quite a few out there with wizards and templates for building blog and forum sites. By the time you outgrow that, hopefully you will have learned what you need to know, or will be able to hire someone to help.