I'm applying a marking system on my small website much like one stackoverflow uses, my real question is - what's the best way to keep tags to ensure that they might be looked and strained?
My idea is:
Table: Items Columns: Item_ID, Title, Content Table: Tags Columns: Title, Item_ID
Is not fast enough? It is possible to better way?
One item will have many tags. And something tag will fit in with many products. This suggests in my experience that you will potentially need a middleman table to beat the numerous-to-many obstacle.
Something similar to:
Posts: Item_ID, Item_Title, Content
Posts: Tag_ID, Tag_Title
Posts: Item_ID, Tag_ID
It may be that the web application is insanely popular and want denormalising in the future, but it is pointless muddying the waters too soon.
You need to read Philipp Keller's blogs about marking database schemas. He tries a couple of and reviews his results, in terms of easy creating common queries, and in relation to performance. Quantity of tags, quantity of labeled products, and quantity of tags per item counseled me factors. The posts come from 2005 I am unaware of any updates since that time.
You will never discuss slowness in line with the data you provided inside a question. And That I don't believe you need to even worry an excessive amount of about performance at this time of developement. It's known as premature optimisation.
However, I'd claim that you'd include Tag_ID column within the Tags table. It's often a good practice that each table comes with an ID column.
I'd suggest using intermediary third table for storing tags<=>items associations, since we now have many-to-many relations between tags and products, i.e. one item could be connected with multiple tags and something tag could be connected with multiple products. HTH, Valve.
If space will probably be an problem, possess a 3rd table Tags(Tag_Id, Title) to keep the written text for that tag after which improve your Tags table to become (Tag_Id, Item_Id). Individuals two values usually supplies a distinctive composite primary key too.
Products must have an "ID" area, and Tags must have an "ID" area (Primary Key, Clustered).
Make medium difficulty table of ItemID/TagID and set the "Perfect Index" on the website.
Really In my opinion p-normalising the tags table may well be a better answer, based on scale.
By doing this, the tags table simply has tagid, itemid, tagname.
You will get duplicate tagnames, however it makes adding/getting rid of/editing tags for specific products Less complicated. It's not necessary to produce a new tag, take away the allocation from the old one and re-allocate a replacement, you simply edit the tagname.
For exhibiting a listing of tags, you just use DISTINCT or GROUP BY, and obviously you can count the number of occasions a tag can be used easily, too.