[eluser]esra[/eluser]
[quote author="jplanet" date="1192874821"]To do this I would have to enforce a rule that every product and category must have a unique value in the static_file field. Then every URL request would run a query to see which table contains the value, and delegate to the applicable function.[/quote]
I believe that Jim assumption about SEO is correct. It's also an assumption on my part but makes logical sense.
You could store your category/subcategory and product content in one table and manage the hierarchies in a separate table using the nested sets model or the adjacency list model. Just use a join to handle the table relationships. For example:
Content columns:
Quote:id
static_name
name
short_description
long_description
...
created
created_by
modified
modified_by
Nested set table columns:
Quote:id
content_id
nleft
nright
Thunder UK has posted a model on the wiki for handling nested sets. The categories example included with the contribution probably could be used as a starting point for your own hierarchies.
Adjacency List columns:
Quote:id
content_id
parent_id
I believe thatthere are multiple posts about using the Adjacency List model (recursion) on the forum but no wiki article that I am aware of.
The combination of id and content_id creates a unique relationship in your hierarchies. Thus, you can handle many-to-many category/product relationships in your queries.