[3.0] Normalization

[Shadowbadger]

I have been tasked to add a male / female segregated board on a forum I administer.

No problem I thought, just setup a CRON task to add the appropriate forum member group based on the gender.  So I go looking for the table of Member IDs and Group IDs, but I could not find it?

Then I found a comma separated list of Group IDs in the members table . . . .

Normalizing things such as this provides much better leverage on the data.  I am using SMF1, perhaps this has changed in SMF2 which I do plan on upgrading to but in my professional experience approaching the task from a database point of view instead of a programmers point of view will yield better results.  Relational databases are designed in such a way as to offer powerful data manipulation.

Utilize it.

[Norv]

Thank you for taking the time to share your thoughts. This didn't change in 2.0, either, although quite a number of backend (and not only) improvements were made. I completely agree though, normalization is the way to go, at most adding some redundancy afterwards, when really needed for performance. That additional groups field is just bad design, and it won't be there in next major versions of SMF.
Please feel free to add any other thoughts on this, examples, alternative schema designs, any help is appreciated.

[Shadowbadger]

Thank you for the reply.

I appreciate that once something like this is in it can be a pain to take out what with existing code relying on the design.

I have found various functions to help me do what I need but just wanted to share and explain the effect of the design, probably 50-100 lines of SQL instead of like 3.

[SleePy]

FYI,
There is a handy addMembersToGroup function in Subs-Membergroups.php.

[Shadowbadger]

Thanks for that.

I would prefer to and am close to achieving it SQL though.  I need to set all of the existing ones plus catch new members as they come in and I would rather do this without touching the SMF codebase.  I am splitting existing ones all out into a temp table, one row per member-group then adding the male / female groups before GROUP_CONCATing them back in.

Adding the correct male / female group every day or every hour is sufficient for my needs so I do not need to build it into the normal group and registration functions.