CODEWITHSUNDEEP
database-designentity-relationship
Ali
Ali

Reputation: 1841

Database relationship when one entity is optional

I am making a school management system. The basics are as follows:

There will be a School table such as: 

id  
school_name

A school will have several branches. So there is Branch table in which I have school_id as foreign key such as: 

id  
branch_name  
school_id

A branch will have several Students (I am skipping Classes and Sections and other stuff to make it simple) so there is a Students table as below:

id
student_name
branch_id

This case is simple where a school must have a branch. I want to know what if a branch is not mandatory? If there is no branch then how will you design the Students table then? Will it have another field for "school_id" in addition to "branch_id" like this? 

id  
student_name  
branch_id  
school_id

So if there is no branch then "branch_id" will be 0 while "school_id" will contain value from parent School table? Is this the correct way?

Upvotes: 0

Views: 792

Answers (3)

nathanvda
nathanvda

Reputation: 50057

Well, you could say that a student always belongs to a school, and secondly to a branch.

So yes, i would indeed propose a student-table as you said:

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]

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29639

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.

Upvotes: 2

nvogel
nvogel

Reputation: 25534

Create a new table like this:

StudentBranch (student_id, branch_id)

student_id would be the key.

Upvotes: 0

Related Questions