I am presently trying to get the best way (in term of usability and gratifaction) when confronted with a scenario like fetching records labeled having a specific tag, or category, or something like that like this.
A great way (generate an income desired to do), is always to fetch records using the tag/category slug, therefore the URL would seem like :
fetching records by slug, which looks much better than :
fetching by ID and adding the slug behind therefore it is more search-engine friendly. That one is much better performance-smart, because fetching data by an integer ID could be faster than the usual string. (cmiiw)
Now, having a db schema like :
posts post_to_tags tags ----- ------------ ---- id id id title post_id name content tag_id slug ... ...
shall we be held doing the work right ? Can there be pitfall or best-practices that I have to know to prevent performance problems ? (eg. tags shouldn't exceed 10,000 records, or tag slug shouldn't exceed n figures, or something like that else)
Thanks ahead of time.
Using the first URL style as well as your current db design, this can be done:
select ... from posts p join posts_to_tags pt on pt.post_id = p.post_id join tags t on t.id = pt.tag_id where t.slug = [url slug value];
As lengthy as tags.slug is indexed, this ought to be extremely powerful, hardly different from
select ... from posts p join posts_to_tags pt on pt.post_id = p.post_id where pt.tag_id = [url tag ID];
The first is much better, but could the slugs often be transformed? For the reason that situation you'd must have a redirect table (e.g. "some-article-about-dogs" has become "article-about-dogs-and-felines").