Re: Questions on Tags table schema

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