Re: Questions on Tags table schema

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 <dev(at)archonet(dot)com> 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