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 11:28:05 +0100
Message-ID: <46ADBD35.4000304@archonet.com> (raw)
In-Reply-To: <d251ee4a0707300313y5e648fa3kdc5df88180df82db@mail.gmail.com>
References: <d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com>
<46AD917E.3050902@archonet.com>
<d251ee4a0707300313y5e648fa3kdc5df88180df82db@mail.gmail.com>
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
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: <46ADBD35.4000304@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