Advertisement:

Author Topic: [3.0] Normalization  (Read 3969 times)

Offline Shadowbadger

  • Newbie
  • *
  • Posts: 3
[3.0] Normalization
« on: October 18, 2011, 10:05:06 AM »
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.
« Last Edit: January 19, 2012, 04:43:53 PM by Norv »

Offline Norv

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 18,313
  • Blue Wolf
Re: Normalization
« Reply #1 on: October 18, 2011, 10:29:36 AM »
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.
« Last Edit: January 19, 2012, 04:43:29 PM by Norv »
To-do lists are for deferral. The more things you write down the later they're done… until you have 100s of lists of things you don't do.
File a security report | Developers' Blog | Bug Tracker

Also known as Norv on D* | Norv N. on G+ | Norv on Github

Offline Shadowbadger

  • Newbie
  • *
  • Posts: 3
Re: Normalization
« Reply #2 on: October 18, 2011, 10:38:07 AM »
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.

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,283
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: [2.1] Normalization
« Reply #3 on: October 18, 2011, 01:59:44 PM »
FYI,
There is a handy addMembersToGroup function in Subs-Membergroups.php.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Shadowbadger

  • Newbie
  • *
  • Posts: 3
Re: [2.1] Normalization
« Reply #4 on: October 18, 2011, 02:36:58 PM »
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.