How to express categorized items in relational tables

Doo Suk Yang wrote:

I'm novice in database world.
Now I have to make design a database where categorized items should be
stored. It resembles the structure something like seen in Yahoo.

Thanks for reading and generous comments of yours.

I do it something like this..
create table category (title text, category oid);
so that categories have parent categories. If the category attribute is
null then it's a parent category. For urllinks (like in yahoo) I have
create table urllink (title text, url text, category oid);

On Thu, 13 May 1999, Chris Bitmead wrote:

Doo Suk Yang wrote:

I'm novice in database world.
Now I have to make design a database where categorized items should be
stored. It resembles the structure something like seen in Yahoo.

Thanks for reading and generous comments of yours.

I do it something like this..
create table category (title text, category oid);
so that categories have parent categories. If the category attribute is
null then it's a parent category. For urllinks (like in yahoo) I have
create table urllink (title text, url text, category oid);

Thank you for your answer. It is going to be working.

BTW, when inserting a parents category I had to go through two steps;
selecting category of parents and inserting new title and selected
category. Of course I'm using Perl and the above two steps are not great
matter. But still I'm curious is it possible to write an SQL statement to
do it.

===============================================================================
Science is the game we play with God to find out what his rules are.
-------------------------------------------------------------------------------
Doo Suk Yang
Research Scientist Voice: 82-42-866-2222
LG Chemical Ltd. Research Park
Biotech Research Institute I FAX: 82-42-861-2566
-------------------------------------------------------------------------------
A fool hath no delight in understanding, but that his heart may discover itself.
(Proverbs 18:2)
===============================================================================

I think you want something like...
INSERT INTO child(foo,bar,baz,category) SELECT 'foo1', 'bar1', 'baz1,
oid FROM category WHERE category.name = 'CategoryFoo';

I'm not sure. Is that what you mean?

Doo Suk Yang wrote:

On Fri, 14 May 1999, Chris Bitmead wrote:

I think you want something like...
INSERT INTO child(foo,bar,baz,category) SELECT 'foo1', 'bar1', 'baz1,
oid FROM category WHERE category.name = 'CategoryFoo';

YES!!! That's exactly what I looked for. :-)

Thank you, thank you so much.

I'm not sure. Is that what you mean?

Doo Suk Yang wrote:

Thank you for your answer. It is going to be working.

BTW, when inserting a parents category I had to go through two steps;
selecting category of parents and inserting new title and selected
category. Of course I'm using Perl and the above two steps are not great
matter. But still I'm curious is it possible to write an SQL statement to
do it.

===============================================================================
Science is the game we play with God to find out what his rules are.
-------------------------------------------------------------------------------
Doo Suk Yang
Research Scientist Voice: 82-42-866-2222
LG Chemical Ltd. Research Park
Biotech Research Institute I FAX: 82-42-861-2566
-------------------------------------------------------------------------------
A fool hath no delight in understanding, but that his heart may discover itself.
(Proverbs 18:2)
===============================================================================

===============================================================================
Science is the game we play with God to find out what his rules are.
-------------------------------------------------------------------------------
Doo Suk Yang
Research Scientist Voice: 82-42-866-2222
LG Chemical Ltd. Research Park
Biotech Research Institute I FAX: 82-42-861-2566
-------------------------------------------------------------------------------
A fool hath no delight in understanding, but that his heart may discover itself.
(Proverbs 18:2)
===============================================================================

On Thu, 13 May 1999, Doo Suk Yang wrote:

I'm novice in database world.
Now I have to make design a database where categorized items should be
stored. It resembles the structure something like seen in Yahoo.

Thanks for reading and generous comments of yours.

Though this _is_ a postgresql list, you could try Catalog, available from
www.senga.org, gpled. There's no postgres in it, though.

cr