Database composite key in MySql

Question

Condition is Each operator record has a unique group code and product code. It may possible that some group has same product code.

main operator table

     id           integer                  PK 
     name         varchar(100)

product group table

     code         integer                  PK
     opt_ID       integer                  PK + Ref. operator
     group        varchar(50)

product table

     code          integer                  PK
     opt_ID        integer                  PK   
     group_ID      integer                  Ref. Group table code field   
     name          carchar(50)              

problem is here what should I make a reference in product table either from operator table or either product table group?

Answer 1

I think what you want to do requires another table.

main operator table
  id           integer                  PK 
  name         varchar(100)             Ref. Product table code field unique
  product_code integer
product group table
  code         integer                  PK
  group        varchar(50)
product table
  code          integer                  PK
  group_ID      integer                  Ref. Group table code field   unique
  name          carchar(50)              

This should allow you to query operators by product or group. Does this make sense for what you are trying to do? If not include a use case.

edit. this will insure a unique product/group tuple for each operator....you access operator.group through joining on the product table.