Re: Questions on Tags table schema
От | Richard Huxton |
---|---|
Тема | Re: Questions on Tags table schema |
Дата | |
Msg-id | 46ADBD35.4000304@archonet.com обсуждение исходный текст |
Ответ на | Re: Questions on Tags table schema ("Jay Kang" <arrival123@gmail.com>) |
Ответы |
Re: Questions on Tags table schema
Re: Questions on Tags table schema |
Список | 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
В списке pgsql-performance по дате отправления: