From: | "Jay Kang" <arrival123(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Questions on Tags table schema |
Date: | 2007-07-29 14:22:30 |
Message-ID: | d251ee4a0707290722k50de5789lad616cb168791058@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 )
tags: database schema (
http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html )
and a forum discussion on tags with a very similar problem:
http://www.webmasterworld.com/forum112/502.htm
But I don't like the solution, would like to stick with serial integer for
Cars, Planes and Schools tables.
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.
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
)
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.
--
Regards,
Jay Kang
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2007-07-29 16:35:37 | Re: RES: select on 1milion register = 6s |
Previous Message | Scott Marlowe | 2007-07-29 04:51:41 | Re: select on 1milion register = 6s |