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