Обсуждение: 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 serial
>
> Table port
>         target          char(10)
>         number          integer
>         port_code       integer  ...could be a serial
>
> Table vlan_port
>         vlan_code       integer
>         port_code       integer
>
> 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).

IMO, the use of artificial single-column integer primary keys (and foreign keys) is far easier to deal with query-wise
andprogramming-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
istruly 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
andeasier to maintain.  It will also make it easier when you discover that you need to keep track of more than just the
nameof 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