Re: Questions on Tags table schema - Mailing list pgsql-performance
| From | Jay Kang |
|---|---|
| Subject | Re: Questions on Tags table schema |
| Date | |
| Msg-id | d251ee4a0707300313y5e648fa3kdc5df88180df82db@mail.gmail.com Whole thread Raw |
| In response to | Re: Questions on Tags table schema (Richard Huxton <dev@archonet.com>) |
| Responses |
Re: Questions on Tags table schema
(Richard Huxton <dev@archonet.com>)
|
| List | pgsql-performance |
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?
--
Regards,
Jay Kang
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@archonet.com > wrote:
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.
Yes, I used school name varchar(64) as primary key for the school tables. You can consider schoolName as Pagename for a wiki.
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
pgsql-performance by date: