Re: Postgresql and programming

On Wed, 14 Jun 2000, Ron Peterson wrote:

> Normalize, normalize, normalize.  Use a relational database for it's
> strengths.  Don't duplicate data.

On a related note, I have a similar scheme where I have a "master" table
which includes integer references to other tables like the previous
example had with authors. So, like the following:

CREATE TABLE master_device_list (
    device_name        CHAR(80),  -- Do people use CHARs or VARCHARs?
    device_param1        INTEGER,
    ...
    device_config_code    INTEGER REFERENCES device_config NOT NULL
                DEFAULT 1
                     /* Sequences seem to start
                        at a default of 1.  Is this
                        always true? Will it remain
                        this way in the future? */
);

CREATE TABLE device_config (
    device_config_code    SERIAL PRIMARY KEY,
    param_1            INTEGER,
    ...
    param_n            INTEGER,
                UNIQUE (param_1, ... , param_n)
);

First, is this an acceptible way to construct a relational database?
99.9% of all of these devices will use the exact same configuration.
Also, addition and removal of data is not that important in terms of
speed.  Data lookup is more important for my application.

Now I want to do updates and adds.  The issue is how to determine if the
device_configuration that I want already exists.  If it does exist, I can
use the following:

UPDATE master_device_list
  SET device_config_code = (
                SELECT device_config_code
                              FROM device_config
                  WHERE param_1 = integer_value AND
                    ...
                    param_n = integer_value
               )
  WHERE device_param1 = some_value;


But this will fail if the entry doesn't exist yet.  Is there some standard
way of handling this sort of thing?  Is it some sort of transaction?

I appreciate any and all help.

Thanks,

R.