sql - MySQL: When To Break Up / Split a Table -

i'm building database , i'm not sure how deep of hierarchy should make.

it seems best case space saving 3 layers. group->sub_group->item

in average scenarios there 300 items subgroup , 100 subgroups groups. items nearing 1 million , growth accelerating.

i'm attached distinguishing group item because reflects real world, sub_group exists because items identical few hundred rows. clear, take data in 1 instance of sub group , attach every instance of item.

would making @ least 3 joins in every query better performance? or better off making less tables more repetitive data?

this isn't mysql question sql rdbms question in general.

you have database normalized eliminate duplication , minimize storage.

you may right normalize , put subgroup info item if:

1. the subgroup data small.
2. you query both subgroup , item information , view isn't sufficing.
3. you have performance issue drive change--don't prematurely optimize.

there other considerations, shoud started.

there not absolute here.