Table Relationships - how to do

1

I'm having trouble understanding how to create the relationship of the tables. My scenario is as below:

A parceiro can have multiple usuários , but a usuário can only be linked to a parceiro . A usuário can register countless pedidos , and those pedidos will be linked usuário that registered and parceiro of the user. Example:% with% a is linked to pedido cesar in which this usuário is linked to usuário partner x .

To make a new parceiro it is necessary to put the code (id) of pedido and the data of the new request, such as: name, rg, cpf and email

In this parceiro there is a pedido in which it is unique and being formed by cpf-partner . In a table chave de vpn has the vpnCode , bytes received, sent, server ip, real ip.

A chave de vpn has VPN , chave de vpn that generated it, parceiro cpf , pedido rg .

What I can not understand (I've been trying to figure out how to do it for more than a week) is how to make those relationships. Please, I need help.

    
asked by anonymous 10.05.2016 / 17:59

1 answer

2

I think this architecture should solve your problem. I did not put all the VPN fields but you can do it yourself.

Ifyouneedthecodethatgeneratesthesetables,seebelow:

CREATESCHEMAIFNOTEXISTS'mydb'DEFAULTCHARACTERSETutf8COLLATEutf8_general_ci;USE'mydb';---------------------------------------------------------Table'mydb'.'PARCEIRO'-------------------------------------------------------CREATETABLEIFNOTEXISTS'mydb'.'PARCEIRO'('pk_parceiro'INTNOTNULLAUTO_INCREMENT,'nome'VARCHAR(45)NULL,'cpf'VARCHAR(45)NULL,'RG'VARCHAR(45)NULL,'E-mail'VARCHAR(45)NULL,'outrosCampos'VARCHAR(45)NULL,PRIMARYKEY('pk_parceiro'))ENGINE=InnoDB;---------------------------------------------------------Table'mydb'.'USER'-------------------------------------------------------CREATETABLEIFNOTEXISTS'mydb'.'USER'('pk_user'INTNOTNULLAUTO_INCREMENT,'pk_father'INTNULL,'Name'VARCHAR(45)NULL,'outrosCampos'VARCHAR(45)NULL,PRIMARYKEY('pk_user'))ENGINE=InnoDB;---------------------------------------------------------Table'mydb'.'USER_PARCEIROS'-------------------------------------------------------CREATETABLEIFNOTEXISTS'mydb'.'USER_PARCEIROS'('fk_user'INTNOTNULL,'fk_parceiro'INTNOTNULL,'outrosCampos'VARCHAR(45)NULL,PRIMARYKEY('fk_user','fk_parceiro'),INDEX'fkp_idx'('fk_parceiro'ASC),CONSTRAINT'fku'FOREIGNKEY('fk_user')REFERENCES'mydb'.'USER'('pk_user')ONDELETENOACTIONONUPDATENOACTION,CONSTRAINT'fkp'FOREIGNKEY('fk_parceiro')REFERENCES'mydb'.'PARCEIRO'('pk_parceiro')ONDELETENOACTIONONUPDATENOACTION)ENGINE=InnoDB;---------------------------------------------------------Table'mydb'.'vpnCode_Parc'-------------------------------------------------------CREATETABLEIFNOTEXISTS'mydb'.'vpnCode_Parc'('pk_vpnCOde'INTNOTNULLAUTO_INCREMENT,'vpnCode'VARCHAR(450)NOTNULL,'fkParceiro'INTNOTNULL,'outrosCampos'VARCHAR(45)NULL,PRIMARYKEY('pk_vpnCOde'),INDEX'fkkpar_idx'('fkParceiro'ASC),CONSTRAINT'fkkpar'FOREIGNKEY('fkParceiro')REFERENCES'mydb'.'PARCEIRO'('pk_parceiro')ONDELETENOACTIONONUPDATENOACTION)ENGINE=InnoDB;

Ifausercanonlyhaveonepartneranditcanbeassociatedwithmultipleusersthenthemodellookslikethis:

    
10.05.2016 / 19:09