Thread: Check existence of combination in an M.N relation

Hi list

I want to store sets of configuration options in an m:n table. Each
option is defined by a parameter/value pair, and each set of options has
a unique ID (the data describes non-default hardware configuration of a
group of computers).

Now, given an arbitrary set of options, how do I check if that exact
combination already exists, and either return the ID if it exists, or
insert the set and return the new ID?

The relation is defined as:

CREATE TABLE mach_conf (
   id int4 NOT NULL,
   param varchar(64) NOT NULL,
   value varchar(256) NOT NULL,
   CONSTRAINT mach_conf_id PRIMARY KEY (id, param, value)
)

Example table:

id   param   value
---------------------
1    hz      2.6G
1    ncpu    8
1    ram     4G
2    hz      2.6G
3    ncpu    4
3    arch    i386

If I have the set [ncpu=4, arch=i386] I want to have id=3 returned. If I
have the set [ncpu=4] I want id=4 returned.

How would I go about this? I'm on PostgreSQL8.2.

Thanks,
Erik

On Fri, Feb 29, 2008 at 10:25 AM, Erik Cederstrand <erik@cederstrand.dk> wrote:
>  How would I go about this?

This smells like EAV.

Please read

http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html

and consider reevaluating the schema according to valid relational
design (tables, columns, check constraints, etc.).

In any case, good luck.