Hey Richard, Thanks again for the reply, its great to hear some feedback. So once again, here we go:
On 7/30/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > 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. Yes, I am aware that postgre is case-insensitive, but I write all query with case so its easier for me to read later on. > 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. I'm developing in c# with asp.net 2.0 which as a membership provider. I'm using ASP.NET 2.0 Website Programming / Problem - Design - Solution" (Wrox Press) <http://www.amazon.com/gp/product/0764584642> as a reference, so not having AddedBy as a foreign key within each of the tables was taken directly from the text. I do not understand your comment about 255 character with 257 being impossible? Could you elaborate, if you feel it warrants further elaboration. > 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). OK, timestamp with time zone it is. To be honest, I've been using postgresql for a while now, but never tried using timestamp with time zone. Also, if it's "AddedDate" why isn't it a date? I had this first as a date, but asp.net 2.0 didn't like it, and changing it to a timestamp fixed the problem. > 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). Are you suggesting to separate the Tags table into Tags and TagDetails? Because ViewCount within Tags table would represent how many times that tag was clicked, I think others would call this field Popularity. I've been reading alot about tags and I am fascinated at all the information about user tags can provide. Where would I put information such as ViewCount, AddedBy, Status, etc if not within the Tags table? Sorry, if I'm totally missing your point. > 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. Yes, within the Cars table CarID would be a serial so it would auto increment with each row. I understand your concern. > 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. Hmm, so if I have a tag called "Saab" and a user clicks on Saab, then information from both Cars and Planes table would appear. If I'm inserting a new row for a tag, wouldn't I need to check if that tagname already appears within the Tags table or would I just create a new row with that tag name. Sorry, I'm not sure what " 'car'::text " this is doing, but I'm guessing its used to group the cars, planes, etc. so it knows which item_type it is. Brilliant! 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> Thanks for the query, I'm going to start programming so I can figure it out as I go along. ... > > -- > Richard Huxton > Archonet Ltd > -- Regards, Jay Kang
