Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9D4449FA508 for ; Sun, 29 Jul 2007 11:22:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 02024-09 for ; Sun, 29 Jul 2007 11:22:26 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by postgresql.org (Postfix) with ESMTP id ADC379FA4F4 for ; Sun, 29 Jul 2007 11:22:32 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id k40so1742136wah for ; Sun, 29 Jul 2007 07:22:31 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=PYK+BpO6kfuStwXKC5uDcUCIebti+nFglpc4cvuHbpsdxfvfERz5bZrZbgSbGJZ3wHLNmZ/DCnau6IfJWz7JHmkgU7e5IScyBYhCHpgoJoitVC8kqYe8SpUjeF5+Uv2Bc6ZFcxTHSy1Ngmk1tftpdlACRfmn6SP4FCyaJf6VsCI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=RybU6Sum5AMqnIdvypIOWwPeqgctJcqADAi4m3I/4rjZ9ASfdnm3fa+syUP83774rr9Ety1k+zcv5jugqz4Sr3TJ2pUchG+JV5IeRO41eKsTDQVn7t0SP/1gmGV60XKVbSkRFp3qZX5guOYXDDBdvok06DclBH9MQp++/iAWJ4o= Received: by 10.115.110.6 with SMTP id n6mr4698340wam.1185718950761; Sun, 29 Jul 2007 07:22:30 -0700 (PDT) Received: by 10.114.39.18 with HTTP; Sun, 29 Jul 2007 07:22:30 -0700 (PDT) Message-ID: Date: Sun, 29 Jul 2007 16:22:30 +0200 From: "Jay Kang" To: pgsql-performance@postgresql.org Subject: Questions on Tags table schema MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20044_2703661.1185718950729" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/411 X-Sequence-Number: 26095 ------=_Part_20044_2703661.1185718950729 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_20044_2703661.1185718950729 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello,

I'm currently trying to decide on a database design for tags in my web 2.0 application. 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 ------=_Part_20044_2703661.1185718950729--