个人想法:
department {
id
name
}
user {
id
name
departmentid
}
contact {
id
name
phone
email
ownerid // address book owner user id
}
// group type: 1-publich, 2-department, 3-private
group {
id
gtype // group type: 1-public, 2-department, 3-private
name
departmentid // 0-no department, public or private
ownerid // 0- public or department group
}
user-contacts {
id
contactid
groupid
ownerid
}
Get contacts by group:
public: select a.* from contact a join user-contacts b on b.contactid = a.id join group c on c.id = b.groupid where a.ownerid= $ownerid and c.gtype = 1;
department: select a.* from contact a join user-contacts b on b.contactid = a.id join group c on (c.id = b.groupid and c.gtype = 2) where c.departmentid = $departmentid;
private: select a.* from contact a join user-contacts b on b.contactid = a.id join group c on c.id = b.groupid where b.gtype = 3 and c.ownerid = $ownerid;