Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Students :ID

Teachers :ID

Schools:ID

Addresses :ID |StarDate |EndDate

AnotherTable :ID

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

Problem 1

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.

Problem 3
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.

Problem 3
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.

share|improve this question
    
This question might be overly broad for dba –  James Jenkins May 31 '13 at 18:07
    
You need to add more tables and fields to store the security model you want to create between the users and the data. –  Jon Seigel Jun 1 '13 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.