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