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 10:28:05
Message-ID: 46ADBD35.4000304@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2007-07-30 10:48:55
Subject: Re: Questions on Tags table schema
Previous:From: Jay KangDate: 2007-07-30 10:13:19
Subject: Re: Questions on Tags table schema

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