Database design about a company with many branches at different location.

Question

1.00/5 (2 votes)

See more:

Eg.
A hospital may has many branches at different location. The name of the hospital for every branches are same.

A patient may make appointment to different branches of the hospital.
I want to keep track a patient visited which branches of the hospital.

Is this the way to do it as shown below?

Thanks for any advice.

What I have tried:

hospital : hospital_id,name,...
appointment : appointment_id,hospital_id,location_id
location : location_id,....

or we can just do it like this, just use address to keep track?

hospital : hospital id,name,address,...
appointment : appointment_id,hospital_id,..etc

Posted 17-Dec-16 0:25am

心心傷心

Updated 17-Dec-16 16:46pm

Nelek

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-12-17T01:50:00

Arrange it so you have the minimum number of links that is consistent with only storing informations once.
So each hospital branch will have a location, and only one, and an appointment will be at a particular branch. But a patient may have several appointments trhough the courtse of his treatment.
So I'd start with three tables:
Branches

ID         INT, IDENTITY, PRIMARY KEY
Name       NVARCHAR(255)
Address    NVARCHAR(MAX)

Patients

ID         INT, IDENTITY, PRIMARY KEY
Name       NVARCHAR(255)
Address    NVARCHAR(MAX)

Appointments

ID         INT, IDENTITY, PRIMARY KEY
BranchID   INT, Foreign key to Branches.ID
PatientID  INT, Foreign key to Patients.ID
At         DATETIME

And work out from there.

Peter Leow · Answer 2 · 2016-12-17T16:46:00

Get your requirements right first, what better way to do this than to walk through a mock up.
If I was to make appointment to this hospital online through your web app, the hospital name will just be a caption on the screen, one of the important choices to make is which branch to go to. Do you have a way for me to choose one from a list of branches based on some descriptive names not a ID code, since you do not have a branch name other than the same hospital name, some kind of unique location name will help.
To make a location name even more indicative, make its address shown on mouse over by the patient. That is also one of the ways to enhance the user experience.
To sum it up, put yourself in the shoes of the patients, do a mock up and walk through it yourself or better still, get the real patients to walk it through.
Once the requirements and UI design are firmed up, then can you go on to designing your database with confidence.

Database design about a company with many branches at different location.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0