Greetings. I'm on the verge designing a new database for electrical components, and I am in need of help. I have designed 3-4 small databases earlier, but these suffer from bad design and I cannot afford bad design for this database (which I surmise will be the biggest I will ever create). My older designs suffer from the "stiff, Excel-look-a-like"-format. That is; fixed column tables that relate to each other, and when a new column is needed, simply an ALTER TABLE <foo> ADD COLUMN was issued. I was told, and experienced it myself, that this is not a durable design, so I need to learn other approaches how to design a new database - i.e. I want to do The Right Thing from the beginning. This database is supposed to be used by many users as a "library" of their own components. What I am thinking of like in this database for electrical components is, first, a table with names: 1N4148, SB640, BY164, and so forth. These are the components' unique name. These components can be of a specific type: Diode, Switching Diode and Bridge Rectifier (for the above three mentioned components). Further on, these can have different manufacturers, footprints, datasheets, etc. Tables for these are doable by me. However, the table that really makes me cringe in fear of un-knowledge is this one: parameters. A parameter for a component can be something that tells the designer that it must exceed a specific amount of voltage. It's also got a unit. For example, the diode 1N4148 has a parameter Vr (reverse voltage) that equals around 40-50 V (volts) depending on the manufacturer. It also has another parameter, If (forward current), that's around 100-120 mA (milliamperes) depending on manufacturer. There may be parameters describing working temperature, maximum rated frequency, and so forth. Different kind of components has different kind of parameters. Parameters for the components are in no way to be fixed. Users are supposed to be able to enter new parameters on their own, re-use already entered components, and so forth. If User #1 adds a 1N4148 with 10 parameters in his "library", another User #2 with 1N4148 may want to add it to his library, but the components may be from different manufactures and may deviate in its parameter values. (This deviation is often very small, but it exists). The problem in this using my old database design-brain (which I must reprogram) is that my first approach would have been something like this: TABLE parameters (id integer, name text, unit text, value ???) "???" denotes where I would fail, hard. If I hardcode 'value' to an integer, that would be fine for values like "1V", "45mA" and so forth, but in the future I might want to add a boolean value as a parameter, or a string, or anything else than integer. Like a float / numeric denoting maximum voltage: "3.3V". So, my question (I think) is: I have really no clue except that to use a separate table for each separate kind of parameter, but since my users are supposed to be able to add new parameter types, how am I supposed to keep track of everything? Can anyone shed some light of how I should setup / link these parameter tables all together, without locking myself into a static and locked design? -- - Rikard -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
