Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group