Thread: Table-design for categories, suggestions needed
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
On Thu, Aug 5, 2010 at 03:30, Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote: > Real example: Top-category "IC", subcat1 "Logic", subcat2 "TTL": A more readable output: # select id, name, subcategory_1, subcategory_2 from categories where id > 3; id | name | subcategory_1 | subcategory_2 ----+-------+---------------+--------------- 4 | IC | | 6 | Logic | 4 | 10 | CMOS | | 6 11 | TTL | | 6 -- - Rikard
On Wed, Aug 4, 2010 at 6:32 PM, Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote: > A more readable output: > > # select id, name, subcategory_1, subcategory_2 from categories where id > 3; > id | name | subcategory_1 | subcategory_2 > ----+-------+---------------+--------------- > 4 | IC | | > 6 | Logic | 4 | > 10 | CMOS | | 6 > 11 | TTL | | 6 Are you trying to model entity inheritance with this design? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Thu, Aug 5, 2010 at 18:57, Richard Broersma <richard.broersma@gmail.com> wrote: > Then in a table (perhaps called componentattributes), you can > enumerate all of the attributes that you want to assign to any given > component. [...] This looks like a nice design - it will probably fit very well. One thing I didn't manage do understand how I would find the categories "above" the selected one. In my first example; IC -> Logic -> TTL. If I add a component using the attribute table, like yours, and set it to "TTL", how should I keep track of the categories above TTL? I realize I was unclear in my original post on this matter, but this is why I did sort of "linked lists" in my category-table to be able to keep track of the whole category chain for a component What I mean is, when I add components I am supposed to pick a top category (IC in this case). Clicking this should bring up subcategories for the category IC (Logic, Microcontrollers, or whatever), and so on until the proper attribute has been found. Perhaps a better example is an Ebay-listing. For a random page I have open right now I see at the top: "Listed in category: Mobile & Home Phones > Mobile Phone Accessories > Other Mobile Phone Accessories > Other Mobile Phone Accessories". This "chain" is what I'm trying to duplicate. -- - Rikard
On Aug 5, 2010, at 3:50 PM, Rikard Bosnjakovic > > Perhaps a better example is an Ebay-listing. For a random page I have > open right now I see at the top: "Listed in category: Mobile & Home > Phones > Mobile Phone Accessories > Other Mobile Phone Accessories > > Other Mobile Phone Accessories". > > This "chain" is what I'm trying to duplicate. Okay, then what you are trying to model is a tree. Postgresql has a special contrib module called Ltree that manages this nicely. Other than this, there are three data models that you can choose from: Adjacency list model Aka BOM model Path enumeration model Nested set model The first model is probably the easiest to grasp. And since pg 8.4 supports recursive queries this model becomes a nicechoice. Sent from my iPod
On Fri, 06 Aug 2010 19:54:01 -0400, Lew <noone@lewscanon.com> wrote: >On 08/05/2010 04:06 AM, gargoyle60 wrote: >> Oops, that should read... >> ... >> FOREIGN KEY (parent_level) >> REFERENCES category (category_level) > >So category (category_level) is a unique or primary key, then. Something like that, probably with (category_level, category_code). Like I said, I never got around to implementing the provisional design and there may be more to it than that.
On 08/05/2010 04:06 AM, gargoyle60 wrote: > Oops, that should read... > ... > FOREIGN KEY (parent_level) > REFERENCES category (category_level) So category (category_level) is a unique or primary key, then. -- Lew
Hi, Think that an error of modeling. Occur this way, where there are 5 types of subcategory in the table, there could be another table containing the information category and subcategory. Got it? exemple: TABLE categories (id, name_category) TABLE subcategories (id, name_subcategory) TABLE components (id, name_component) TABLE category_subcategory (id_category references categories(id), id_subcategory references subcategories(id) ) I think it would be better to model your system. Bye, Victor Hugo 2010/8/7 gargoyle60 <gargoyle60@example.invalid>: > On Fri, 06 Aug 2010 19:54:01 -0400, Lew <noone@lewscanon.com> wrote: > >>On 08/05/2010 04:06 AM, gargoyle60 wrote: >>> Oops, that should read... >>> ... >>> FOREIGN KEY (parent_level) >>> REFERENCES category (category_level) >> >>So category (category_level) is a unique or primary key, then. > > Something like that, probably with (category_level, category_code). > Like I said, I never got around to implementing the provisional design and there may be more to it > than that. > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > -- []´s Victor Hugo
gargoyle60 wrote: >>> Oops, that should read... >>> ... >>> FOREIGN KEY (parent_level) >>> REFERENCES category (category_level) Lew wrote: >> So category (category_level) is a unique or primary key, then. gargoyle60 wrote: > Something like that, It would have to be exactly like that or you wouldn't be able to point a foreign key to it. > probably with (category_level, category_code). Then you'll need a two-column foreign key. > Like I said, I never got around to implementing the provisional design and there may be more to it > than that. -- Lew
Le Sat, 7 Aug 2010 09:27:26 -0300,
Victor Hugo <vh.clemente@gmail.com> a écrit :
> Hi,
>
> Think that an error of modeling. Occur this way, where there are 5
> types of subcategory in the table, there could be another table
> containing the information category and subcategory. Got it?
>
> exemple:
>
> TABLE categories (id, name_category)
> TABLE subcategories (id, name_subcategory)
> TABLE components (id, name_component)
> TABLE category_subcategory (id_category references categories(id),
> id_subcategory references subcategories(id) )
As they are components one ref can be of different technologies, it depends
on the choosen notation but it would be more efficient to use a generic
notation such as 74144, instead of using specific notation: 74LS144,
74HC144, 74L144...
Thus, creating a new table for technologies and table 'component'
(remember: notation rules say to never use the plural) should be:
CREATE TABLE tech (
id SERIAL PRIMARY KEY,
name VARCHAR(128) NOT NULL CHECK(char_length(name)>5)
);
CREATE TABLE component (
id SERIAL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
tech INTEGER REFERENCES tech(id) NOT NULL
);
--
Common sense is instinct, and enough of it is genius.
-- Josh Billings