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

On Sat, Jul 3, 2010 at 03:51, Craig Ringer <craig(at)postnewspapers(dot)com(dot)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