Group & Sub-Group

[Pico_Train]

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]

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]

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]

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!