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.