homepage Welcome to WebmasterWorld Guest from 54.166.108.167
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Group & Sub-Group
Pico_Train




msg:4301110
 7:33 am on Apr 20, 2011 (gmt 0)

Hi,

This is more a MySQL question but am doing everythin in PHP.

I have to do a bit of work where a main Group and a sub-group of the main group will be involved. The main group can have many sub-groups but the sub-group only has 1 main group.

So my question is, in MySQL, in the DB, is it better to create 2 tables, 1 main group and 1 sub-group with a foreign id to the main group.

OR

1 table with everything and a field where the foreign id relating to the main group is either null or the id of the main group.

What's better? I'm pretty sure I know already but would love to hear your views.

Thanks!

 

mbabuskov




msg:4301205
 11:41 am on Apr 20, 2011 (gmt 0)

If you often need to select groups and subgroups at once, use a single table.
If groups and subgroups have different fields, or you often need to only select groups, use two tables.

rocknbil




msg:4301403
 5:50 pm on Apr 20, 2011 (gmt 0)

A metaphor is categories and subcategories, correct?

Two tables will allow you to place an item in two different subcategories as well. You would just have an extra row in the subcategories table.

The main group can have many sub-groups but the sub-group only has 1 main group.


Visualize this, with one table how would you maintain "many sub groups?" Multiple subgroup fields (sg_1,sg_2,sg_3) which limits the number of subgroups you can have without modifying the table? A comma separated list that you have to explode in programming? That would work, but it would have to be varchar, and you want to use as many integer fields as possible. With one table, if some groups don't have a sub-group, what have you got? A bunch of empty fields. With two tables, the record just doesn't exist.

My rule of thumb: if a base table has large amounts of redundant data or empty fields (or the potential for having it so), those values need to be in another table joined on the base table unique identifier. This is generally one of the concepts behind normalization.

I'd structure it like this:

maingroups
id|name|description

subgroups
id|maingroup_id|name|description

Whatever uses it (users, products, whatever)
id|userid|name|email

user_maingroups
id|userid|group_id

user_subgroups
id|userid|subgroup_id

If a member is in a subgroup, you only need to record it in user_subgroups and pick up that main group's name, etc. from the subgroup and maingroup tables (no entry needed in user_maingroups.) If there's no subgroup for a given main group, they would only have an entry in the user_maingroups table.

The select statements would be a little more complex involving lots of joins, but the database would be lean and mean, no redundant data, no unnecessary empty fields.

Pico_Train




msg:4301783
 9:44 am on Apr 21, 2011 (gmt 0)

thanks for taking the time to elaborate and in the end that is exactly what I did. I knew it was the right thing but got my knickers in a twist thinking I could do it easier with one table.

That was the easy part, the rest which should have taken 2 hours to code, took me 6!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved