Multiple boards, multiple profiles...one member database!

[Gwydion Frost]

"Actually, Compuart and several other people have worked on having forums with "multiple outlets".  There are easy and hard ways of doing this, including:
  - using separate tables for each forum (causes avatar problems, yes.)
  - using a column to specify which "outlet" to use (very easy, Compuart did this one.)"

Ok, not to bore everyone with a long detailed request, but looking at the various means of doing this, the solution that is the least talked about, seems to be the one that may provide me the best option...

Basically, I am looking at the concept of creating a multiple forums website, across 3 separate domains, each nested within their own subdomains.

The main domain: members sign up using their real names. This is a domain for writers, artists, musicians, coders, etc to get together and create project groups.

Domain #2: The gaming domain. This is the one with a dozen or so subdomains, each dedicated to their own genre of gaming board. Members' profiles are show their RPG CHARACTER NAME. To do this, I want to custom tweak the profiles and figure out a way that they will only show the fields that pertain to the particular forum...so that "Bob from Ohio" on the main board will post as "Elohas the Elf" on the Medieval Fantasy board, "Texas Pete the gunslinger" on the Wild West board, and "Superguy" on the superhero themed board. Figuring out how to get the avatars to change between boards would be sweet as well...LOL

Domain #3: Also consisting of several subdomains...the Product boards...fan boards for the "TV network" we are building, fan boards (and requests) for the various artists who have their wares for sale on this domain. Undecided as to whether I should use Domain #1 profiles for these, or allow the artists to create their BRAND names... (some of us write and create under pen/stage names, you know...)

So, now that you know what I am going to do with the wonderful SMF forum programming...would you recommend (for speed purposes, naturally) the shared database approach (as touted here and here and even here where there is an intriguing variation...

...or, do you think Compuart's CMS would be better? And if so, where can I get more info about it?

Now, as far as the profile tweaking goes, boy...that is an interesting mess in and off itself...

Not only do I want to tweak it so that it displays a different identity on each board, but I would love for it to automatically register you into the appropriate usergroups (important for the Compendium on the Medieval Fantasy board: Races and regions will dictate exactly what information you can get from the Compendium, so you only "know" what your character would...keeping Out-Of-Character knowledge out of your hands...) as you pick and choose from the various races and regions that they are from.

Fact is, these forums will eventually bloat with a lot of traffic (read: members), and content (read: posts), so I am looking at something that will make sure that the inquiries to the databases don't bog down the server's performance time...

Your help (or at the very least, your advice) in these matters would be greatly appreciated.

[Compuart]

I think the least complicated and best scalable solution would be to create separate installs of SMF, using separate tables, but share the members table. The only reason for sharing all tables, would be if you want to share common boards. Especially when it comes to speed and scalability, 3 separate communities of 100,000 messages are a lot faster than a shared community of 300,000 messages.

The way to achieve it, is relatively simple, by replacing all references in the source code of {$db_prefix}members into shared_members. You might do the same for {$db_prefix}membergroups if you like the membergroups to be the same on each site, and {$db_prefix}log_activity for the statistics.

[Gwydion Frost]

OK, sorry for taking so long on getting back to you...

It sounds simple.

Disturbingly simple.

So let's see if I am overlooking something here...

If each SMF board is using it's own mysql database...

Where do I place the shared tables...and how do I direct each board to reference a separate database table...?

[Compuart]

Everything can be in the same database. The only difference for each install is the prefix of the tables.

smf_attachments
smf_ban_groups
etc.

smf2_attachments
smf2_ban_groups
etc.

smf3_attachments
smf3_ban_groups
etc.

[Gwydion Frost]

Especially when it comes to speed and scalability, 3 separate communities of 100,000 messages are a lot faster than a shared community of 300,000 messages.

Now, maybe, once again, I am not grasping this...but the way this is written tells me that I would be better off with separate databases, to keep the inquiries down, and speed up.

And yet, the solution suggested tells me that I only need ONE database, simply accessing 3 duplicate (yet separate due to prefixes) tables.

Now, am I wrong in thinking that it is the inquiries to the database that slow the board down...and that having multiple databases versus a single database would be the faster solution?

Don't get me wrong...I grasp the idea of the separate installs accessing specific tables within the database...I just seem to be failing to grasp how the database speed will be optimal if 300 people on each board are pulling inquiries for each board all at the same time from one database...! Isn't that going to place a load on said database and server? Would having them on separate databases lessen that load...?

[Sheepy]

Well, since MySQL can reuse the db connection most or all of the time, a single db may be better?

[drhamad]

Especially when it comes to speed and scalability, 3 separate communities of 100,000 messages are a lot faster than a shared community of 300,000 messages.

Now, maybe, once again, I am not grasping this...but the way this is written tells me that I would be better off with separate databases, to keep the inquiries down, and speed up.

And yet, the solution suggested tells me that I only need ONE database, simply accessing 3 duplicate (yet separate due to prefixes) tables.

Now, am I wrong in thinking that it is the inquiries to the database that slow the board down...and that having multiple databases versus a single database would be the faster solution?

Don't get me wrong...I grasp the idea of the separate installs accessing specific tables within the database...I just seem to be failing to grasp how the database speed will be optimal if 300 people on each board are pulling inquiries for each board all at the same time from one database...! Isn't that going to place a load on said database and server? Would having them on separate databases lessen that load...?

You'd be using one db with 3 different sets of tables.  I believe what he is referencing is the ease of the system accessing any given message within 100k x3 vs 300k x 1, in a given table.

I obviously use the first method you referenced.  It's nice, it causes no problems with profiles or avatars etc, but it does mean you're sharing profiles across the entire set of boards.

For my use, it's perfect, except one major flaw - in my case, I need search engines to see the 4 seperate domains I use, and they more prefer to see it all as one site.  In your case, it is all one site, just different sections, so that isn't an issue for you.

[Compuart]

Now, maybe, once again, I am not grasping this...but the way this is written tells me that I would be better off with separate databases, to keep the inquiries down, and speed up.

And yet, the solution suggested tells me that I only need ONE database, simply accessing 3 duplicate (yet separate due to prefixes) tables.

MySQL accesses the database on table level for write access and on row level for read access (in general). This means that whenever you create a message your messages table will be locked for a little while. A locked message table is not accessible, making other processes have to wait. The bigger the tables are, the more they rely on performance, not to mention searching a table. That's why 3x100k is in general faster than 1 x 300k.