Search code examples
mysqldatabase-designinner-joinforeign-key-relationshipdatabase-normalization

Correct database implementation of groups like Google's circles


create table USERS (  
    PK_USER int not null auto_increment primary key,  
    EMAIL varchar(100) not null,  
    PASSWORD char(40) not null  
);  

create table CIRCLES (  
    PK_CIRCLE int not null auto_increment primary key,   
    CIRCLE varchar(45),  
    FK_CREATOR int,  
    foreign key(FK_CREADOR) references USERS(PK_USER)     
);  

create table MEMBERS (  
    FK_MEMBERS int,  
    FK_CIRCLES int,  
    foreign key(FK_MEMBER) references USERS(PK_USER),  
    foreign key(FK_CIRCLE) references CIRCLES(PK_CIRCLE)  
);  

is correct to have this dual reference to PK_USER? (for ref. USERS=USUARIOS, MEMBERS=MIEMBROS, CIRCLES=CIRCULOS, CREATOR=CREADOR)


Solution

  • By what I can «infer» they are playing two different roles :

    1. In CIRCULOS you're tracking WHO did the provisioning of the entity
    2. In MIEMBROS you're tracking the «membership» relation (a many-to-many relationship)

    So: you're ok provided those were your intentions :)