pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jay Kang <arrival123@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Questions on Tags table schema
Date: Sun, 29 Jul 2007 16:22:30 +0200
Message-ID: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com> (raw)

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

view thread (9+ messages)  latest in thread

Message-ID: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>
Permalink:  ../d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com/
Also on:    postgresql.org/message-id/d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: arrival123@gmail.com
  Subject: Re: Questions on Tags table schema
  In-Reply-To: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>
  References: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox