I tried searching google and these forums, but couldn’t find any answers for a similar situation.
I am building a new database and trying to determine if there are any real/noticeable speed/performance advantages among the following setups. Assuming roughly 50 records per subcategory per group (20 subcategories per category), 1,000 records per category per group (10 categories), 1,000 groups per database.
Estimating approximately 1,000 records per query (50 records per subcategory times 20 groups).
Setup 1: 1 overall table with 10,000,000 records - 1 table queried
Setup 2: 10 category tables with 1,000,000 records - 1 table queried
Setup 3: 1,000 group tables with 10,000 records - 20 tables queried
Setup 4: 10,000 group/category tables with 1,000 records - 20 tables queried
My brain’s a little fried so math might be a little off - but gives the general idea.
Thank you for your help in figuring this out.