Creating Surrogate Key table with other unique constraints

Question

I am self-taught for database design and MYSQL. Thank you for looking at my question. I am currently have a database looks like this in mySQL:

The reason I used a composite key is I want keep Owner and DevName unique all the time. One problem I can see is that I need to use two foreign keys all the time so Data1 table is not normalized.

So I made a table looks like this, making another table that has a surrogate key table.

Does this look okay? I am not sure making new table is really a good idea...

Is there any to make them in a single table that has a surrogate key, keeping a combination of Owner and DevName unique like this?:

Answer 1

The Device3 and Data3 looks good to me. I wouldn't mess with adding an extra table unless there was some compelling reason to. And likely any compelling reason to create third table boils down to just dealing with the composite foreign key, like in the original Device/Data1 model.

It seems like all that is missing is UNIQUE constraint. You can add a UNIQUE constraint (i.e. UNIQUE INDEX) on the combination of owner and devname columns. e.g.

 CREATE TABLE device3 
 ( id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
 , owner    VARCHAR(30) NOT NULL COMMENT ''
 , devname  VARCHAR(30) NOT NULL COMMENT ''
 , ...

 , UNIQUE KEY device3_UX1 (owner, devname)

 ) ...

Or, if you already have the device3 table created, you can just add add a unique index to the existing table. e.g.

 CREATE UNIQUE INDEX device3_UX1 ON device3 (owner, devname)