db design question
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.
Richard Harvey Chapman wrote:
Table vlan
target char(10)
id integer
vlan_code integer ...could be a serialTable port
target char(10)
number integer
port_code integer ...could be a serialTable vlan_port
vlan_code integer
port_code integerPrimary 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).
IMO, the use of artificial single-column integer primary keys (and foreign keys) is far easier to deal with query-wise and programming-wise than the real multi-column keys. You will save yourself a lot of pain using single ints.
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?
I don't know what target is in reality (a device??), but if vlan.target and port.target are the same thingy, and target is truly an attribute of both vlan and port in reality, you might consider an additional 'target' (or device?) table, eg,
Table target
target_id serial,
target_name char(10)
and then replace port.target and vlan.target with port.target_id and vlan.target_id, respectively. This will be faster and easier to maintain. It will also make it easier when you discover that you need to keep track of more than just the name of the target.
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?
Are you using 7.0? Version 7 is highly desirable over 6.5*, and ref. int. stuff is new in 7.0...
Regards,
Ed Loehr