Re: New DB-design - help and documentation pointers appreciated

On Sat, Jul 3, 2010 at 03:51, Craig Ringer <craig@postnewspapers.com.au> wrote:

[...]
> You've hit one of those areas where SQL databases kind of suck. You'll
> have to use one of the well understood workarounds like EAV and live
> with their limitations, or find a database better suited to the data.

Thanks for the feedback Craig.

After careful considerations I have decided to ditch the idea of
heavilly using parameters and go back to my initial idea of having a
simple components index instead.

What I've come up to is this structure:

-- Logos, etc, users should be able to upload photos of the components
CREATE TABLE images (
    image_filename varchar(32) NOT NULL,
    image_filesize integer DEFAULT 0,
    image_uploaded timestamp with time zone DEFAULT (localtimestamp),
    image_id serial PRIMARY KEY
);

CREATE TABLE manufacturers (
    manufacturer_name varchar(32) NOT NULL,
    manufacturer_url varchar(32),
    manufacturer_logo integer REFERENCES images(image_id),
    manufacturer_id serial PRIMARY KEY
);

-- E.g. "transistors", "diodes", "amplifiers"
CREATE TABLE categories (
    category_name varchar(32) NOT NULL,
    category_id serial PRIMARY KEY
);

-- E.g. "PNP", "NPN", "JFET"
CREATE TABLE subcategories (
    subcategory_name varchar(32) NOT NULL,
    subcategory_category integer REFERENCES categories(category_id),
    subcategory_id serial PRIMARY KEY
);

-- PDIP, SO, QFN, etc.
CREATE TABLE packages (
    package_name varchar(32) NOT NULL,
    package_image integer REFERENCES images(image_id),
    package_id serial PRIMARY KEY
);

CREATE TABLE users (
    user_name varchar(32) NOT NULL,
    user_password varchar(32),
    user_id serial PRIMARY KEY
);

CREATE TABLE datasheets (
    datasheet_filename varchar(32) NOT NULL,
    datasheet_filesize integer DEFAULT 0,
    datasheet_uploaded timestamp with time zone DEFAULT (localtimestamp),
    datasheet_id serial PRIMARY KEY
);

CREATE TABLE components (
    component_name varchar(32) NOT NULL,
    component_manufacturer integer REFERENCES manufacturers(manufacturer_id),
    component_category integer REFERENCES categories(category_id),
    component_subcategory integer REFERENCES subcategories(subcategory_id),
    component_package integer REFERENCES packages(package_id),
    component_pincount smallint,
    component_owner integer REFERENCES users(user_id),
    component_image integer REFERENCES images(image_id),
    component_datasheet integer REFERENCES datasheets(datasheet_id),
    component_comment text,
    component_scrap boolean DEFAULT FALSE,
    component_id serial PRIMARY KEY
);

Same kind of components can have different manufacturers, that's why
I'm not having any hard constraints anywhere (like UNIQUE in
component_name). Some examples that I want to be able to store:

Different manufacturers and packaging:

Fairchild 74LS14 DIP14 (hole mounted IC with 14 pins)
National  74LS14 SO14  (surface mounted IC with 14 pins)

Different subcategories / packages:

BC547, category transistors, subcategory NPN, package TO-92
BC547, category transistors, subcategory NPN, package TO-220
BC557, category transistors, subcategory PNP, package TO-92

and so forth. The point is that one component name can exist in many
different flavors. I have still not yet come up to a definite solution
how the subcategories will be implemented, so this is still just a
draft.

However, I feel that this design is the same design I seem to use for
all my databases, and in the end I always find that I designed them
wrong from the beginning. The table "components" feels like that one
is going to be locked into a corner; it seems to "fixed" and not as
flexible as I want this database to be. In the future I will probably
want to add more relations without having to make zillions of ugly
patches to the design.

I would therefore appreciate any feedback on this table structure and
how I can improve it even further.

Thanks.


--
- Rikard