I have requirement in one of the project where different entities like Teacher,School,Student Store addresses in single table and maintain their relationship.
Example -School can add teacher and store their address information.
-Teacher store their other address information.
-Teacher can add the student address information.
-Student can add their other address information.
-Teacher can mark their address information to School entity.
Those which are created by the each entity for others are visible to then and owner himself. Example School enter teacher address information are their address where other school cannot view it.
The teacher view it but could not edit it. If Teacher create address which are added in the school entities are private copies of teacher. School can view it but not edit it.
Problem is how to map those tables.
Addresses :ID |StarDate |EndDate
Other Fields which have relationship between each entities(e.g who added it and from whom) (private and public relationship).
Comment on Security model and problem
Here i want to store address of School,Teacher and Student
a.First way is to store nullable FK of StudentID,TeacherID and SchoolID Problem of having 3 nullable foreign keys in the address table. b.Second way is to create separate table like below SchoolAddress ID | StudentID | AddressID TeacherAddress ID | TeacherID | AddressID SchoolAddress ID | SchoolID | AddressID c.Third way i can do is create a Person table. Person ID type(student or teacher) Addresses ID SchoolID PersonID i like c methologies so that in future if i have another entities i can mapped to type field and enhance it.
How to solve the security model issues if above entities and add each other address like below
School - added/update address- of - teacher (own by school no other entity can view it except teacher ) Student - added/update address- of - teacher (own by Student no other entity can edit it) Teacher - added/update address- of - student Teacher - added/update address- of - School. Teacher - share it public and have their own address information and share to other school too.
Which made me to think have option b or separt table of each entities.
How to solve issue related to who own it. I was thinking of created junction table for who own it and entity relatioship.
Junction Table ID | AddressID | FromPersonID | ToPersonID
But how to maintain Student relation with Teacher.