Field Repetition Rule
I'm doing a DB for my dad, he's a doctor and he wants to have all patient files and notes... everything.
I have patient table with: patientID, name, lastname, evrything... and phone table with phone_name and phone_type, and patientID (as FK from patient table), the fields values for phone_type are: [Home 1 | Home 2 | Office 1 | Office 2 | etc. ]
My question is how I can make that for a patient, if I choose phone type Home 1, and then in other field, choose it again Home 1, says that that value is repeated, but when I change to another patient I could choose again Home 1 without restriction.
In Phone table ID's speaking:
patientID | phone_type
----1---- | --Home 1
----1---- | --Home 2
----2---- | --Home 1
----2---- | --Office 2
But to restrict when I insert the phone type value if I repeat Home 1 for patientID 1, to make a message saying that is repeated and you need to change it, or something like that.