php - Duplicated Rows with UNIQUE INDEX MySQL and NULL Columns -

table sufix: id, name

(3, 'com') 

table domainname: id, name

(2, 'microsoft') 

table domain: id, name_code, sufix

(1, 2, 3)    -- microsoft.com 

table subdomainname:

(4, 'windows') 

table subdomain: id, name_code, domain

(7, 4, 1)     -- windows.microsoft.com 

table email: id, name, atserver

(3, 'myemail', 7)    -- myemail@windows.microsoft.com (4, 'other', 1)      -- other@microsoft.com 

here problem of foreign key constraints. how can resolve domains , subdomains create emails correctly? i'm having problems unique index null values, example, solution maybe:

table email: id, name, subdomain, domain

(3, 'myemail', 7, null)  -- myemail@windows.microsoft.com (4, 'other', null, 1)    -- other@microsoft.com 

but

(5, 'newemail', null, null)  -- duplicated values in table (6, 'newemail', null, null) (7, 'newemail', null, null) (8, 'newemail', null, null) 

and

(**3**, 'myemail', 7, 1)   -- myemail@windows.microsoft.com , myemail@microsoft.com 

how (5, 'newemail', domain_id/subdomain_id, 'domain/subdomain')

so have

(5, 'newemail', 7, 'subdomain') or (5, 'newemail', 1, 'domain')

you still left join subdomain , domain table data 1 need based on 'domain/subdomain' field.

that quick solution. imho db structure not , optimized. should keep domain/subdomain records in 1 table , use emails. table should table fulldomain: id, name_code, domain_name, subdomain_name

(1, 3, 2, 4) -- windows.microsoft.com

or

(1, 3, 2, 0) -- microsoft.com