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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
