Architecture for multiple web apps and databases.

Question

We used to have only one web app, but now we are breaking it down into multiple ones. Each one will be packaged as separate product (web app) Some have things in common some do not.

It was originally coded with php and using Postgresql 8.4 and CodeIgniter as the framework.

I am looking for some good suggestions on how I should set up multiple web apps. They all have their own somewhat unique data. Some data in the databases can be common to some apps but not all. All the apps will be on one server and will have some kind of API to manipulate data.

I want it to be structured such that one User account can access any product they purchase. (kinda like google accounts)

I do not know if its a good idea to have multiple database, or just to have one big one. eventually we will be using S3 for some videos and other images.

Your thoughts and suggestions are much appreciated.

Answer 1

About the database, you could use different SCHEMA's in PostgreSQL to seperate the different apps. Then you could have a database with only one schema for one application, but also many schemas for many applications.

PostgreSQL can handle very large databases, a couple of TB's is not a problem at all.

Answer 2

A single database would probably be cleaner if only because you could reuse the same connection when querying across multiple apps, and JOIN on them without any hitches. For example, adding a list of relevant products or videos in the sidebar of a blog entry, tied together by a common tag system. Or adding the ability to follow a Wave in Reader or a Mail thread, and easily reference spreadsheets & docs between them, for the Google comparison.

The total database size shouldn't affect anything, each table is a separate file independent of others. Conflicting/confusing table names might be the main issue as new features are added throughout, but a little namespacing goes a long way.

Answer 3

Thanks for the replies. I can see the database being distributed over lots of drives because we are not allowed to remove any data for up to 10 years.

Also we have lots of images( I forgot to mention this in the original question), Nothing like flikr but a decent amount. We have almost 300gigs now and because of a new business deal we are looking at around 500 new members a I month. Right now the images are stored in multiple folders depending on they group. If the hard drive runs out of room where/ and how would i access them? I'm assuming this is where load balancers come in.