Search code examples
mysqldatabase-schema

Mysql schemas vs table name prefixes

My mysql DB is growing and I want to structure the tables into domains like I do in the code.

I see two ways to do this:

  1. prefix the table names:

For module specific tables I could rename them from e.g. participants, events, ... to calendar_participants, calendar_events, ...

SELECT * FROM calendar_events;
  1. use separate schemas:

then I would have a separate schema/database for all calendar related tables and prefix the tables with the schema name in all my queries.

SELECT * FROM calendar.events;

Are there any drawbacks from using schemas instead of prefixes?

  • Is there a performance impact when joining tables from different schemas compared to joins in a single schema?
  • Can I reference foreign keys across schemas?
  • ...
Solution

  • No, there are no performance drawbacks to using different schemas, and yes the foreign keys work fine across schemas.

    Using separate schemas provides you with some extra tools. For example you can DROP or dump a schema.