MySQL database design: sharing data between 2 or more database

[dee-u]

For instance, I have a Human Resource Information System, a Library System and an Enrollment System, those systems share common data (Instructors and Students), how should I design it such as they will only get the Instructors and Students data from one table only? Could I separate them in 3 database and just "join" using a query? If yes, how could I enforce referential integrity between those databases? Or should they be in just one database only?

Your inputs are most welcome.

[jmcilhinney]

I'm not sure what the actual problem is. You can have as many applications connect to the same database as you want, if they all use the same data. I don't use MySQL much but in SQL Server you can add multiple schema to a single database in order to logically segregate data somewhat. I would expect that MySQL would provide something similar.

For instance, I work on two applications named PHISCo and PeriPH that share a database. That database has a 'common' schema for shared objects, and 'phisco' and 'periph' schema for application-specific objects. There's no specific need to do that but it does mean that you can use the same name for two tables, e.g. we have a Setting table in both the 'phisco' and 'periph' schema. You can also limit user access by schema, e.g. the user that the PHISCo application connects as has access only to the 'phisco' and 'common' schema. Again, not sure what MySQL offers but I'd expect something similar.

[dee-u]

It's been a while since I last used SQL Server and I don't know how that "schema" works. Google seems to tell me that such is not applicable to MySQL? Combining all those tables may make the database "messy", it maybe difficult to track which tables is used by each application.

[topshot]

In MySQL, schema and database are essentially synonymous so not like Oracle and I believe SQL Server. Nevertheless, the MySQL server can have multiple database/schema that are designed just like jmc said.

I have essentially identical schema/database for each client on our webserver. If I had a need I could do joins in queries. I have never tried cascading deletes across schemas, but you could certainly test it easily enough.