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

Re: Questions on Tags table schema

From: Richard Huxton <dev(at)archonet(dot)com>
To: Jay Kang <arrival123(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Questions on Tags table schema
Date: 2007-07-30 07:21:34
Message-ID: 46AD917E.3050902@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Jay Kang wrote:
> 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 )

And what in particular recommended this to you?

> 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.

You don't mention a primary-key here at all. You're not thinking of 
using "schoolname" are you?

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

What's wrong with the completely standard:
   car_tags (carid, tagid)
   plane_tags (planeid, tagid)
   school_tags (schoolid, tagid)

> 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.

Sorry, don't understand this question.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-performance by date

Next:From: Jay KangDate: 2007-07-30 10:13:19
Subject: Re: Questions on Tags table schema
Previous:From: Decibel!Date: 2007-07-30 06:52:41
Subject: Re: RES: RES: select on 1milion register = 6s

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