Table-design for categories, suggestions needed

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Table-design for categories, suggestions needed
Date: 2010-08-05 01:30:42
Message-ID: AANLkTinTtT95023MV=psMYWmgv_OBv=uR_TAP5Z=McQ3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm playing with a database for electronic components, and I need a
way to have components in different categories / subcategories.
However, I do not know which is the best way to implement such
relations.

My first, "linear", idea was this:

TABLE categories (id, name)
TABLE subcategories (id, name, subcategory references categories(id))
TABLE components (id, name, category references categories(id),
subcategory1 references subcategories(id), subcategory2 references
subcategories(id), ...)

This way will certainly work, but I'm afraid that if I in the future
need to add yet another subcategory, I will shoot myself in the foot.

Therefore I re-did the idea by implementing all categories (including
subcategories) in one single table, like this:

CREATE TABLE categories (
id serial PRIMARY KEY,
name varchar(32) NOT NULL,
subcategory_1 integer REFERENCES categories(id),
subcategory_2 integer REFERENCES categories(id),
subcategory_3 integer REFERENCES categories(id),
subcategory_4 integer REFERENCES categories(id),
subcategory_5 integer REFERENCES categories(id)
);

This design will ofcourse require that the top-level category is added
first, then the subcategories (in increasing order).

Real example: Top-category "IC", subcat1 "Logic", subcat2 "TTL":

# select * from categories where id > 3;
id | name | subcategory_1 | subcategory_2 | subcategory_3 |
subcategory_4 | subcategory_5
----+-------+---------------+---------------+---------------+---------------+---------------
4 | IC | | | |
|
6 | Logic | 4 | | |
|
10 | CMOS | | 6 | |
|
11 | TTL | | 6 | |
|
(4 rows)

I guess finding the top-level category for a subcat 5-row will be a
pain in the backside using this way; first getting id of the row, then
id for subcat 5, then subcat 4 and so forth.

Is this Really Bad Design? I'm all ears for better suggestions.

Thanks.

--
- Rikard

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Rikard Bosnjakovic 2010-08-05 01:32:30 Re: Table-design for categories, suggestions needed
Previous Message LaboRecherche 2010-08-04 16:22:51 Re: Looking for SKYLINE command