Hello, I'm currently trying to decide on a database design for tags in my web 2.0application. The problem I'm facing is that I have 3 separate tables i.e. cars, planes, and schools. All three tables need to interact with the tags, so there will only be one universal set of tags for the three tables.
I read a lot about tags and the best articles I found were: Road to Web 2.0 ( http://wyome.com/docs/Road_to_Web_2.0:_The_Database_Design ) tags: database schema ( http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html ) and a forum discussion on tags with a very similar problem: http://www.webmasterworld.com/forum112/502.htm But I don't like the solution, would like to stick with serial integer for Cars, Planes and Schools tables. Currently, this is my DB design: Cars (carid, carname, text, etc.) Planes (planeid, planename, text, etc.) Schools (schoolname, text, etc.) <------ School does not take int as primary key but a varchar. Tags (tagid, tagname, etc) --- Now here is where I have the question. I have to link up three separate tables to use Tags --- So when a new car is created in the Cars table, should I insert that carID into the TagsItems table --- as itemID? So something like this? TagsItems ( tagid INT NOT NULL REFERENCES Tags.TagID, itemid INT NULL, <---- really references Cars.carID and Planes.planeID schoolname varchar NULL <---- Saves the Schools.schoolname itemid + tagId as Unique ) I also have a question on the schoolname field, because it accepts varchar not integer. There seems to be some design that would better fit my needs. I'm asking you guys for a little assistance. -- Regards, Jay Kang
