Skip site navigation (1) Skip section navigation (2)

Questions on Tags table schema

From: "Jay Kang" <arrival123(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Questions on Tags table schema
Date: 2007-07-29 14:22:30
Message-ID: (view raw or whole thread)
Lists: pgsql-performance

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
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 ( )

tags: database schema ( )

and a forum discussion on tags with a very similar problem:
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?

  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.

Jay Kang


pgsql-performance by date

Next:From: Decibel!Date: 2007-07-29 16:35:37
Subject: Re: RES: select on 1milion register = 6s
Previous:From: Scott MarloweDate: 2007-07-29 04:51:41
Subject: Re: select on 1milion register = 6s

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group