Re: Questions on Tags table schema

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:

CREATE TABLE Tags
(
TagID serial NOT NULL,
TagName varchar(64) NOT NULL,
AddedBy varchar(256) NOT NULL,
AddedDate timestamp NOT NULL,
Status int NOT NULL,
ViewCount int NOT NULL CONSTRAINT DF_tm_Tags_ViewCount DEFAULT (('0'))
);

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:

CREATE TABLE car_tags
(
CarID integer NOT NULL,
TagID integer NOT NULL
);

CREATE TABLE plane_tags
(
PlaneID integer NOT NULL,
TagID integer NOT NULL
);

CREATE TABLE school_tags
(
SchoolID integer NOT NULL,
TagID integer NOT NULL
);

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?

On 7/30/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> Jay Kang wrote:
> > Hello,
> >
> > I'm currently trying to decide on a database design for tags in my web
> > 2.0application. The problem I'm facing is that I have 3 separate
> > tables
> > i.e. cars, planes, and schools. All three tables need to interact with
> the
> > tags, so there will only be one universal set of tags for the three
> tables.
> >
> > I read a lot about tags and the best articles I found were:
> >
> > Road to Web 2.0 (
> http://wyome.com/docs/Road_to_Web_2.0:_The_Database_Design )
>
> And what in particular recommended this to you?

The Road to Web 2.0 is an example of tag implementation, just thought it
would be helpful to someone with the same problem that I have.

> Currently, this is my DB design:
> >
> > Cars (carid, carname, text, etc.)
> > Planes (planeid, planename, text, etc.)
> > Schools (schoolname, text, etc.) <------ School does not take int as
> primary
> > key but a varchar.
>
> You don't mention a primary-key here at all. You're not thinking of
> using "schoolname" are you?

Yes, I used school name varchar(64) as primary key for the school tables.
You can consider schoolName as Pagename for a wiki.

> Tags (tagid, tagname, etc)
> >
> > --- Now here is where I have the question. I have to link up three
> separate
> > tables to use Tags
> > --- So when a new car is created in the Cars table, should I insert that
>
> > carID into the TagsItems table
> > --- as itemID? So something like this?
> >
> > TagsItems
> > (
> > tagid INT NOT NULL REFERENCES Tags.TagID,
> > itemid INT NULL, <---- really references Cars.carID and
> Planes.planeID
> > schoolname varchar NULL <---- Saves the Schools.schoolname
> > itemid + tagId as Unique
> > )
>
> What's wrong with the completely standard:
> car_tags (carid, tagid)
> plane_tags (planeid, tagid)
> school_tags (schoolid, tagid)

> I also have a question on the schoolname field, because it accepts varchar
> > not integer. There seems to be some design that would better fit my
> needs.
> > I'm asking you guys for a little assistance.
>
> Sorry, don't understand this question.
>
> --
> Richard Huxton
> Archonet Ltd
>

--
Regards,
Jay Kang