From: Richard Huxton <dev@archonet.com>
To: Jay Kang <arrival123@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Questions on Tags table schema
Date: Mon, 30 Jul 2007 08:21:34 +0100
Message-ID: <46AD917E.3050902@archonet.com> (raw)
In-Reply-To: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>
References: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, arrival123@gmail.com
Subject: Re: Questions on Tags table schema
In-Reply-To: <46AD917E.3050902@archonet.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