Tags and Tables

Tiddly_Winks

So I'm starting to design/layout my tables for my first FULL site I'm starting from scratch and I'm stuck on how I should handle my Tags feature.

Basically members will be able to tag different things like their profile, courses, equipment and pro profiles.

I've done some searching and it looks like the 2 table design is the consensus best method but I thought I would get some thoughts here before I moved on.

With this method you have a "Tags: Tag_ID, name" table and "Tag Map: id, ref_to, tag_id" table.

ref_to would be the ID of whatever the user associated with that tag. So if they added it to their own profile it would ref_to=their profile id.

Is this the right direction or am I way off?

Be gentle!

Roger_Ramjet

So it's 3 tables: users, tags, tag map

Yes that is the way to resolve the many to many relationship between users and tags. You've got this right, well done.

Tiddly_Winks

Cool!

Yeah I guess it's 3 tables but the third table could be users or it could be courses or it could be equipment. Just depends on what is being "tagged" with the added tag.

On another note.

Why is it that for user account with profiles this gets broken up into 3 tables with. User, User_Profile and User_Status as the 3 tables.

Couldn't this all be in one table pretty easily?

laserlight

You might want to read the PHP Builder article Database Normalization and Design Techniques to get a better foundation in database design. As a guess, your "user account with profiles" should be broken up into at least two tables because there is a one-many relationship between user accounts and profiles.