Re: db design question

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 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