Question :
I have two tables
- doctor (doc_id, Doc_name, Doc_phone)
- Specialization (doc_id, specialization)
a doctor can have zero or many specializations, but I’m not sure what the primary key for Specialization would be.
Answer :
Can a Specialization exist without a Doctor? I suspect it can, in which case you need a third table, to “master” these Specializations and your Doctor/Specialization becomes a Weak Entity joining those two:
select * from doctor ;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | Fred Flintstone | They retrained :-)
| 2 | Barney Rubble |
+----+-----------------+
select * from specialization ;
+----+------------+
| id | name |
+----+------------+
| 33 | Neurology |
| 44 | Psychiatry |
| 55 | Radiology |
+----+------------+
select * from doctor_specialization ;
+--------+---------+
| doc_id | spec_id |
+--------+---------+
| 1 | 33 |
| 2 | 55 |
+--------+---------+
Your Primary Key for doctor_specialization is the entire record – you can’t have a record that doesn’t have both values present, nor can you have duplicates of their combination.
Further, each field has a Foreign Key back to its [own] parent table.
primary key ( doc_id, spec_id )
foreign key ( doc_id ) references doctor ( id )
foreign key ( spec_id ) references specialization (id )
If you needed to find a doctor with a given Specialization, you might consider a Covering Index going back the other way:
create index i_ds_spec
on doctor_specialization ( spec_id, doc_id ) ;
A primary key can be compound (consist of multiple columns), and even cover the entire table (be all of the table’s columns).
In your case, (doc_id, specialization) is the primary key.
The key for this should be DOC_ID & SPECIALIZATION (ID).
This should be able to identify records for this table uniquely. Read this as “For every doctor there is a specialization and for every specialization there is a doctor in this table”