On Sat, Jul 3, 2010 at 03:51, Craig Ringer <cr...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
