Table of Contents

MySQL shared tables for multi-league setup

In one of the leagues I'm applying the website we have 3 different leagues running under the same organisation. We would like to have the ratings calculated over all the games played, which requires a more complex database setup. Some of the core tables and some of the league specific tables need to be shared, which can be achieved with MySQL's Views.

Share tables

  1. Choose the “master” site from which the users will be shared (this is the database which will actually store the data).
  2. Copy the $secret value from the master site's configuration.php file to the same file on all the “slave” sites.
  3. Ensure access to the master site's database is given to the users of each of the slave sites' databases.
  4. Run the following commands on each slave site table:
    DROP TABLE ipl_users
CREATE VIEW ipl_users AS SELECT * FROM master.ipl_users
  5. If you need to connect leagues with existing data, you need to adjust IDs in the following tables: xxx
  6. You can check whether all primary keys are consecutive with the following SQL query:
    SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM master AS a, master AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
  7. List of tables to be shared:
Joomla 1.5 Core Joomla 3.x Core Bethico League Extension
ipl_users ipl_users ipl_ipl_bar
ipl_session ipl_session ipl_ipl_date
ipl_stats_agents ipl_usergroups ipl_ipl_game
ipl_core_acl_aro ipl_user_notes ipl_ipl_map
ipl_core_acl_aro_groups ipl_user_profiles ipl_ipl_match
ipl_core_acl_aro_map ipl_user_usergroup_map ipl_ipl_player
ipl_core_acl_aro_sections ipl_viewlevels ipl_ipl_player_game
ipl_core_acl_groups_aro_map ipl_ipl_season
ipl_ipl_site
ipl_ipl_tournament

Other settings