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").