Message-ID: <46ADBD35.4000304@archonet.com>
Date: Mon, 30 Jul 2007 11:28:05 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Jay Kang <arrival123@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Questions on Tags table schema
References: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>	
	<46AD917E.3050902@archonet.com>
	<d251ee4a0707300313y5e648fa3kdc5df88180df82db@mail.gmail.com>
In-Reply-To: <d251ee4a0707300313y5e648fa3kdc5df88180df82db@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jay Kang wrote:
> Thanks for the reply Richard, but I guess I didn't explain myself well. I
> have three tables that needs to be mapped to the Tags table. Most of the web
> references that I mentioned only maps one table to the Tags table. Here is
> my Tags table:

One quick point. SQL is case-insensitive unless you double-quote 
identifiers. This means CamelCase tend not to be used. So instead of 
AddedBy you'd more commonly see added_by.

> CREATE TABLE Tags
> (
>    TagID serial NOT NULL,
>    TagName varchar(64) NOT NULL,
>    AddedBy varchar(256) NOT NULL,

This is supposed to be a user? But it's not a foreign-key, and you've 
decided that 255 characters will be a good length, but 257 is impossible.

>    AddedDate timestamp NOT NULL,

You probably want "timestamp with time zone" (which represents an 
absolute time) rather than without time-zone (which means 1pm in London 
is different from 1pm in New York).

Also, if it's "AddedDate" why isn't it a date?

>    Status int NOT NULL,
>    ViewCount int NOT NULL CONSTRAINT DF_tm_Tags_ViewCount  DEFAULT (('0'))
> );

You might not want to mix in details about number of views with details 
of the tag. Particularly if you might record more details later (when 
viewed, by whom etc).

> Is it your opinion that the most standard solution for my problem would be
> to create three separate tables called car_tags, plane_tags and school_tags,
> which maps to each of the tables:

Well, yes.

> CREATE TABLE car_tags
> (
>    CarID integer NOT NULL,
>    TagID integer NOT NULL
> );
[snip other table defs]

Don't forget CarID isn't really an integer (I mean, you're not going to 
be doing sums with car id's are you?) it's actually just a unique code. 
Of course, computers are particularly fast at dealing with 32-bit integers.

> Would TagID for each of these three tables be a foreign key for the Tags
> table? Also would each CarID, PlaneID, and SchoolID be a foreign for each
> corresponding tables? Also won't getting tags for three tables be more
> complicated? Isn't there a better solution or is this wishful thinking?

Yes, yes, and no.

You have cars which have tags and planes which have tags. Tagging a 
plane is not the same as tagging a car. Either you confuse that issue, 
or you want separate tables to track each relationship.

Fetching a list of everything with a specific tag is straightforward enough:

SELECT 'car'::text AS item_type, car_id AS item_id, carname AS item_name
FROM cars JOIN car_tags WHERE tag_id = <x>
UNION ALL
SELECT 'plane'::text AS item_type, plane_id AS item_id, planename AS 
item_name
FROM planes JOIN plane_tags WHERE tag_id = <x>
...

-- 
   Richard Huxton
   Archonet Ltd