Re: 2 Unique FKs in single Table

[Manuel Maurer]

> Are those all the columns in those tables? It
> almost never makes sense to have a 1-column
> table.
No, there are several other columns in the tables. I just stripped it down to the necessary columns as some of them contain confidential information which I cannot post on the internet and I was too lazy to rename them all.

> 3 unique keys for a single table is almost always
> never useful. Can you get it down to 1, or
> _maybe_ 2?
You are referring to pcb_sensor? I read a lot about primary keys in the last days and stripped it down to this:

CREATE TABLE `pcb_sensor` (
`pcb_id` BIGINT UNSIGNED NOT NULL,
`sensor_id` BIGINT UNSIGNED NOT NULL,
`position` ENUM('S1', 'S2') NOT NULL,
PRIMARY KEY (`pcb_id`, `position`),
UNIQUE INDEX `sensor_id_UNIQUE` (`sensor_id` ASC)
)

> Is `sensor_pcb` a many:many mapping table? Or
> 'normalization'?
Normalization. I agree that it would require less keys if I add a sensor_id field in pcb, however, as I pointed out this table contains much more information and thus would violate 2NF.

> Forget about the FOREIGN KEYs until we straighten
> out the layout of the data.
As I said I design with MySQL Workbench. When I place the relationships the foreign keys are automatically generated.

Thanks for your help. For the moment I am not going to develop this model much further. This was more of a feasability study (and a bit of training for myself), probably this will be developed further by professional database designers (and most likely in Oracle instead of MySQL).