db design question

From: Richard Harvey Chapman <hchapman(at)3gfp(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: db design question
Date: 2000-06-15 22:09:44
Message-ID: Pine.LNX.4.10.10006151433080.27718-100000@smile.3gfp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have two tables "vlan" and "port" which relate to each other in an N:N
relation. In other words, one vlan can have anywhere from one to N ports
in it, and the reverse is true for ports; one can have 1 to N vlans that
it belongs to. My problem is how to link these two tables. My solution
was the following:

Table vlan
target char(10)
id integer
vlan_code integer ...could be a serial

Table port
target char(10)
number integer
port_code integer ...could be a serial

Table vlan_port
vlan_code integer
port_code integer

The target is required in at least one of the tables since the tables
store vlans and ports for many different devices. Devices may use the
same vlan numbers without the like-numbered vlans being the same vlan, and
ports will obviously be duplicated for every device.

Primary keys: If I use the _code's, I can make them the primary keys, or
I can make the primary key a combination of the target and id/number. If
I do the latter, how would link them together in vlan_port (assuming no
_code fields).

Duplication of the target field: I figured it might be useful to have that
piece of data stored in both tables to make selects on the individual
tables, based on target (the selects), a little bit faster. Does this
even matter? Also, I tried to make the target fields reference another
table, but I received this:
"NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented."
Is there an alternative?

Thanks for the help,

R.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ed Loehr 2000-06-15 22:59:33 Re: db design question
Previous Message W. van den Akker 2000-06-15 22:09:32 Locked record.