Database relationship when one entity is optional?

Interesting question. The situation you describe isn't a straightforward fit for most relational databases. You have a bunch of options I would suggest introducing a default branch for each school, and associating the student with that branch.

This kinda breaks your logical model - you suggest that in the business domain, some schools have no branches. Not sure how you define "school" and "branch", but if this situation is about "virtual" branches for internet learning, you could create a branch called "internet". For simplicity, this is the easiest way to go - if you want to know which students belong to a school, you always run the same query; if you want to know which school a student belongs to, you always run the same query If the business domain really does require this distinction, I'd suggest a table called StudentAssociation > - studentID > - schoolID > - branchID This allows you to factor out the relationships between students and institutions, and you can create database triggers to allow only one association (a student belongs either to a branch, or to a school, but not to both) By factoring StudentAssociation into a separate table, you can also track history - add columns reflecting the dates, and you can track a student's movement between branches or schools.

Interesting question. The situation you describe isn't a straightforward fit for most relational databases. You have a bunch of options.

I would suggest introducing a default branch for each school, and associating the student with that branch. This kinda breaks your logical model - you suggest that in the business domain, some schools have no branches. Not sure how you define "school" and "branch", but if this situation is about "virtual" branches for internet learning, you could create a branch called "internet".

For simplicity, this is the easiest way to go - if you want to know which students belong to a school, you always run the same query; if you want to know which school a student belongs to, you always run the same query. If the business domain really does require this distinction, I'd suggest a table called > StudentAssociation > - studentID > - schoolID > - branchID This allows you to factor out the relationships between students and institutions, and you can create database triggers to allow only one association (a student belongs either to a branch, or to a school, but not to both). By factoring StudentAssociation into a separate table, you can also track history - add columns reflecting the dates, and you can track a student's movement between branches or schools.

I already got answer to this question and i.e. Use "parent_id" field in School table. There is no need to have a separate Branch table.

If a school doesn't have any branch then its "parent_id" will be 0. If it is a branch then it will contain "parent_id" of its school. – Ali Mar 31 at 17:11 You're welcome.

– Neville K Mar 31 at 18:27.

Create a new table like this: StudentBranch (student_id, branch_id) student_id would be the key.

Well, you could say that a student always belongs to a school, and secondly to a branch. So yes, I would indeed propose a id name school_id (NOT NULL) branch_id (could be NULL) A student will only be part of 1 school and possibly 1 branch, so I see no need to extract into a different link-table. In the case you want to maintain a history, I'd rather introduce a table keeping the history of previous schools/branches, but keep the current school/branch immediately retrievable inside the student-table.

I am not sure what data you would keep at school, and which at branch level. Just to make sure you can treat each student in the same way, you could define a view that handles that for you. E.g.

Something like create view student_v as select student. Name, branch. Name, ... from student, branch if student.

Branch_id not null union select student. Name, school. Name, ... from student, school if student.

Branch_id is null pseudo sql, but you get the drift I hope.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.