How do I migrate from one to many to many/many relationship in SQL

Question

I have a case scenario where I have a Doctor and Patient relationship. A Doctor can have many patients and a Patient can have multiple doctors (usually 1 or 2) but can be more.

How do I structure this in SQL?

Also I can add a one to many pretty simple using dapper.

Insert DoctorId into Client Table. 

So this gives me multiple clients that can have the same doctor and this is correct.

Now on the flip side I cant assign those Clients back into the DoctorTable as that will create a circular reference.

So to retrieve the data currently I am doing

Select * from Clients where DoctorId = xxxx

This gives me all clients that have the same doctor which is fine however, What if I am a client and I need to pull all of the Doctors that are assigned to me

Answer 1

If you want to make your tables more normalized, please add Identity PK for each table, and you could try to add a bridge table to combine these two tables, like this.

DocTable
DoctorId  DoctorName
1         Jim
2         Joe
3         Tom

ClientTable
ClientId  ClientName
1         ABC
2         DEF
3         GHI
4         JKL

Bridge table
id ClientId DocId
1  1        1
2  1        2
3  2        1
4  2        3
5  3        2
6  4        1
7  4        2

Good thing about bridge table is you could add foreign key constraint on this table to make sure no dirty inserts. If you keep all information into one table, then it's hard to read and manage.

Answer 2

You can model a many-to-many relationship using a bridge table - add a table with columns ClientID, DoctorID. Add multiple columns or flags if you need to maintain the activity of a relationship or its effective date. You can get some information on Google using a "bridge tables sql" query.