I am planning to keep patient data in a patient table. I will have multiple Clients registered with me, and each client will have multiple doctors. Should I make patient table client wise or a single table with client and doctor ID field?
It is better to have single
Each doctor must have different
UserId(or call PhysicianId) and then make entry in
patient table with
FkPhysicianId as a column and you can have one table for storing clinic information(auto incremented key can be used as
ClinicId) and one for storing physicians Information like
single patient table will be better as you don't need to add table for each practice you add and data can queried using joins on these table and maintaining too many table in future will be problomatic and other implementation in the EMR application as well.
I would suggest to have a Master Table to assign auto incremented Id to
patient as well.