Re: Table-design for categories, suggestions needed

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