I'm looking for an opinion.

I have 5 websites that all run on php/mysql.

They all have very similar database tables (most of them are exactly the same).

Which option would you choose and why?

1. Each site has it's own database

2. All sites use the same database and each table would have a sitename column which tells you to which site the row belongs.

Looking forward to your opinions.

All though you could do either, it does not give you any more benifit to share the same database unless you want an easier way to share same data content. The downside to that is the possible chance to have a very large database that will be more difficult to backup, restore, maintain, etc.

Graphic, what do you mean with "very large"? mysqldump does not bother even if your db size is 10G.

Originally Posted by carlg

2. All sites use the same database and each table would have a sitename column which tells you to which site the row belongs.

That would only be neccessary if tables were shared between sites. The more common practice for this would be to prefix your table names with the same thing for each site, example table names:

site1_users
site1_products
site1_stuff
site2_users
site2_products
site2_whatever

Each site has it's own database - is more faster and more better for the mysql server...

"Each site has it's own database" is the best option, imagination if a hacker was annoyed at your website and decided they wanted to attack the database. If they did get in, they only take out one website

How many times your databases were hacked? How many people do you know who's databases were hacked? Talking about hacking databases you should always remember that it takes time and efforts to hack anything and it will only be done if the data inside is valueable enough to spend time and efforts on it. Hacking just for fun usually (in 99% cases) means that website developer or db administrator or somebody else was too stupid to setup database access privileges properly. And obviously it cannot be called "hack" when some account has 123 or qwerty as password or no password at all.

Originally Posted by vectorialpx

Each site has it's own database - is more faster and more better for the mysql server...

Do you still run your webserver on P-III 500MHz with 64M RAM?

at 3000 visitors per day and 12000 records you can have a PVIII
and, it depend of the project... at 30 users per day and 1000 records you can make 8 sites in one database... not just 2 but you must think, it you get something to work you need more than that and big sites have more than 2 databases for one project [security, usability and... it's faster]

and, the security problem remains

I think it would be better to keep databases separate for each site unless you want to share content . First of all it is logical. Second, lets say due to a bug in code of one of your websites, you managed to corrupt records for one of your website, imagine the efforts you'd have to put to restore the correct for that particular site. What if in future you decide to change the way the data is stord for a particular site, again you'd have to put unneccssary extra effort. Also, what's the need to add an extra lookup (even if it is based on an index), when you can easily avoid it.

I would choose "Each site has it's own database" for these reasons:

1) Less data for the MySQL engine to have to sift through to compose a page, thereby increasing maximum load speed.

2) If customizations are made to a specific site, one does not necessarily have to consider the effect on any other site.

3) Sites which do not store data that is used in other sites would have blank data in the table, thereby wasting space with virtually no gain.

4) Data which is used for statistical calculations could be stored in a table with the same structure so that data could be exported from the live server, recomposed on a data mining server, and then queried using MERGE table so that data mining does not impact the live server and during the data mining process, only necessary data is parsed.

5) If you have to move one site to another server (due to excessive bandwidth usage, for example), then such move does not need to consider the other sites and unnecessary data does not also have to be moved in order to maintain your functional structure.

I have to agree with the above, keep them seperate unless you want to share anything, so if you wanted to have the user system shared then it might be okay to do it else no point.

I agree with the two above.

I'd go with different databases, unless the sites are more-or-less exactly the same.