[BIZ] Database designers.. what's the best way to.....
 

Set up multiple databases and scripts to use some common data. For example, let's say I have a bunch of different scripts (that should/could be standalone), but they all need some of the same data.. plus they can all add to that data... eg: let's say I'm storing a bunch of domains.. Multiple scripts (Traffic trading, TGP, Thumb etc.) on multiple sites need to read and write to "a" list of domains. Ideally I want it to be the same list so I have one central comprehensive listing.

Should that data be in it's own DB and all the scripts take the extra connection hit to access it and write to it and also lose the ability to perform queries by joining that data with other data in the script specific DB?

Should each script have it's own copy of the data and when a particular script adds a new piece of data it does it to all of the other DBs at the same time?

Is there a better option? Any ways to make these options more efficient?

I'm using MySQL.

Could use replication, though it may not always be up to date.. and I imagine you will lose some transaction locking ability.

Will it be on the same server? If so, you can have different databases and access them without opening new connections. Cross-database joins also work fine in mysql.

Example:

you create three databases: common, site1, site2

You can do queries such as:

SELECT a.field1, a.field2, b.field2 FROM site1.table1 a JOIN common.table1 b ON a.field1=b.field1;

Basically, in your queries that use more than one database just have the dbname in the query before the table name.

Hope that answers your question.

I see nothing wrong with using one database and have scripts connect to it. Make sure your scripts use persistent connections and if you're doing a lot of updates make sure to use InnoDB instead of INNODB table type. Also make sure to set up your primary keys and indeces. If you're doing lots of updates the table should have a numeric primary key (auto_increment) and do updates based on the primary key.

I've never tried cross database joins.. I'll have to give that a go as that would work very nicely. How do you handle the DB connections in this case? Which linkid would you use?

What are the benifits/drawbacks to InnoDb instead of INNODB?

I pretty much always use auto_increment primary key Ids in my tables.

Open your connection to any of the database names, doesn't matter. Or none at all even like this with native php mysql functions:

$sock = mysql_connect('host','user','pass');
$sql = "whatever";
$res = mysql_query($sql,$sock);

In that case though you must make sure your query specifies the db name, otherwise you'd get an error.

For rows that change often or for big tables use InnoDB. For reference tables, that say have few rows that never change, choose INNODB.

Best you check mysql.com for full comparisons between all engine types though.

create more databases
or tables :)