Multiple tables or single?
Posted: Sat Feb 14, 2009 6:13 pm
I'm working on a project right now that has a weird schema (at least to me it seems amatuerish).
Basically It's a business directory with each business listed in several tables
Essentially 5 levels. Would a table such as:
Be an efficient approach considering there are millions of records?
Where an example might be:
A breadcrumb for such a structure might look like:
I believe the way it is setup now, is something like
This seems awfully complex and not very sensible but I'm wondering if maybe it's more efficient or something??? When dealing with millions of records?
Seems it would be fast enough to use a single table and depending on the ID of the record selected working backwards to determine the parents would be fast as well?
My only concern right now, is that businesses can be listed under multiple categories and that is perhaps why they chose this multiple table schema.
Comments?
Basically It's a business directory with each business listed in several tables
Code: Select all
Provinces, Cities, Major Category, Minor Category, Sub-CategoryCode: Select all
pkid, ppid, nameWhere an example might be:
Code: Select all
1, 0, Manitoba
2, 1, Winnipeg
3, 2, Business & Professional
4, 3, Advertising
5, 4, Internet
Code: Select all
Manitoba > Winnipeg > Business & Professional > Advertising > InternetCode: Select all
Major:
pkid, name
Minor:
pkid, major_id, name
Sub:
pkid, major_id, minor_id, nameSeems it would be fast enough to use a single table and depending on the ID of the record selected working backwards to determine the parents would be fast as well?
My only concern right now, is that businesses can be listed under multiple categories and that is perhaps why they chose this multiple table schema.
Comments?