Thread: database design of products catalogue
Hi! I would like to create a database for products catalogue. I have many products with different specification. e.g. lamp A: Part no, Color, Raw Material, Wave length, Vf(V)20mA, lv(ucd)10mA ECG:Part no, Type, Iv, KHz, A, W, Im Should I put these products in one table, or in separate? Best regards, Terence _______________________________________________________________________ Do You Yahoo!? Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk
Hi, Terence Ng wrote: > Hi! > > I would like to create a database for products > catalogue. I have many products with different > specification. > > e.g. > lamp A: Part no, Color, Raw Material, Wave length, > Vf(V)20mA, lv(ucd)10mA > > ECG:Part no, Type, Iv, KHz, A, W, Im > > Should I put these products in one table, or in > separate? Several posibilities. One I'd give a go would be one table for products and their common data products (products_id int8, description text, ...); attributenames (attr_id int4, importance int2, attrname text (or attrname int4 pointing to translation table) ,unit_id) attributeunits (unit_id int4, unitname text, baseunit_id int4, factor numeric) attributes (product_id int8, attr_id int4, unit_id int4, attrtxt text, attrvalue numeric) I think you get the picture :-) Good luck Tino Wildenhain
On Mon, 2003-07-21 at 07:04, Terence Ng wrote: > Hi! > > I would like to create a database for products > catalogue. I have many products with different > specification. > > e.g. > lamp A: Part no, Color, Raw Material, Wave length, > Vf(V)20mA, lv(ucd)10mA > > ECG:Part no, Type, Iv, KHz, A, W, Im > > Should I put these products in one table, or in > separate? You need a good book/web site on database design and normalization. *Basically*, though, every item that describes a product should go in the "product" table. Then, every different color (and, say, whether it pertains to "lamp" or "desk") should go in a "color" table. Likewise with "raw_material", "wavelength" and "voltage" tables. Then, using Foreign Keys, you can link the product table to the static fact tables to ensure the integrity of the data. -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
On 21/07/2003 13:04 Terence Ng wrote: > Hi! > > I would like to create a database for products > catalogue. I have many products with different > specification. > > e.g. > lamp A: Part no, Color, Raw Material, Wave length, > Vf(V)20mA, lv(ucd)10mA > > ECG:Part no, Type, Iv, KHz, A, W, Im > > Should I put these products in one table, or in > separate? > You might be able to do it as one table. Whether that would be the best design, I cannot say. Personally I would approach the problem from 2 different angles. Firstly I would would list all the possible attributes a part could possibly have. For parts which don't possess a particular attribute, store a null in that column. Draw up a grid or spreadsheet of columns/parts and put an X in every column which would be null for a particular part. If you get lots of X's then a single table might not be the best solution. Secondly, look at the application from the user's perspective (the use cases). What attributes are to be displayed for different parts? Are they all similar? Do the screens make separate tables seem more natural? HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+