Best Practice for sharing tables across applications

Question

We have a single MySQL instance running in our small office. We have 3 different applications that are all using the db as their backing store. A single schema has ALL the tables for all three applications. All of the applications use some common tables (e.g. tbl_users, tbl_facilities, etc.). I've been using an application prefix for schema objects to visually separate them from the other applications object, for example:

• foo_tbl_settings
• foo_tbl_orders
• foo_vw_recent_orders
• doof_tbl_settings
• doff_vw_parts

I've never been satisfied with this, it always felt like i should be using separate schemas; one for each logical application then a shared schema for shared objects (user table, etc.)

Sharing common tables is very important and I don't want to give it up.

I've done a little googling for this topic but not found anything that really explained if this was a good practice or not. I was hoping some of you pros could advise me on the best practices for a situation like mine.

Answer 1

Actually, sharing tables is not a very good behavior.

But if you really need that you can create different schema for every application and create a common schema shared_schema for example.

The common schema can have the shared tables.

Then in every application schema you can create mysql views for every table you want to connect from the shared schema. You can name every view by the the name you need in this schema. Now you can have tables described with different names.

Answer 2

To be honest, unless there is a compelling reason to share one DB, could you not just have one DB per application?

You've said they 'share' tables, but then you've described table names with different names, suggesting that whilst they share the same structure they don't actually share the data. Unless I'm misinterpreting things I'd suggest having one DB per application. This would also then allow you to move the DB and application to a new location without having to de-couple it from the other applications on the server.