Multi Campaign DB

[Mirviriam]

I'm doing a quick and dirty ruby on rails project to test some things for another project.

The question I had here on how to implement multiple campaign's into the same database comes up again.

I'm pondering a separate campaign cross connect table (pivot) for every table or faster method, stick the campaign_id into already existing cross connect tables.

Example: Of the 2nd method (not the names I'd use or full listing of fields)

Table #1: campaign_id, campaign_name
x_table_1_2: campaign_id, person_id, isDM? //every participating in campaign & unlimited # of dms
Table #2: person_id, person_name, username, password_hash, etc
Table #3: province_id, province_name, province_owner_domain_id, etc
x_table_3_4: province_id, domain_id, campaign_id // because if multiple campaigns we could have identical pairings of the first two ids
Table #4: domain_id, domain_name, treasury_gold, treasury_rp, etc
x_table_2_4: domain_id, person_id, campaign_id // multiple regents of domain & campaign allowance
x_table_4_5: domain_id, holding_id, campaign_id // because if multiple campaigns we could have identical pairings of the domain & holding id
Table #5: holding_id, holding_name, holding_level, etc

I like the idea of doing it fast ... but I also like the idea of better normalization (campaign_id mapping to all other table's by their id in it's own cross or pivot table).

What do you guys think?

More normalized version would be the same as above, but remove campaign_id from x_table and have a second x_table_camaign (which holds campaign_id & id of whatever type it connects too)

[Vicente]

Why not using just a single campaign per DB to avoid the extra where and column?

[Mirviriam]

Because none of the current companies (which were big enough to get noticed by me) that run web board games do, due to many database transactional costs. SQL servers can handle thousands of databases on their cluster, but a single sql database is built to hold millions of records without slowing down. Additionally, most hosting sites have database limits.

I'd like to build it so if I do enjoy the work and have time - it would be usable by people besides me later on!