New DB-design - help and documentation pointers appreciated

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: New DB-design - help and documentation pointers appreciated
Date: 2010-07-02 18:43:48
Message-ID: AANLkTimOIj5NzaArq_MG0PxsdfmQdLSGXTnF6c5vgZDi@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2010-07-02 19:33:30 Re: bulk insert using COPY and PHP code
Previous Message Jason Dixon 2010-07-02 18:39:18 CFP for Surge Scalability Conference 2010