Bumsik Kim Bumsik Kim - 3 months ago 8
SQL Question

Creating Surrogate Key table with other unique constraints

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
:

enter image description here

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.

enter image description here

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

enter image description here

Answer

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