-2

Will the table be in normal form if in a table there are two foreign keys referencing the same primary key from another table?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Pratistha Kansakar
  • 1
  • 1
  • If your design needs it, it is normal. – Eldar Dec 15 '19 at 16:34
  • Yes, that's a normalized design, but not all DBMSs will support that construction (e.g., SQL Server) or cascading updates and deletions through that structure (MS-Access). – rd_nielsen Dec 15 '19 at 16:59
  • The NF that a table is in has nothing to do with FKs or other tables. Whether a FK can be declared is independent of other FKs. PS "Normalized" & "in normal form" without more details don't mean anything in particular. Also you don't seem to know what it means. Please quote a definition for exactly what you mean by "in normal form" & explain why you are stuck using it. Does your question have anything to do with NFs? Maybe you don't mean "in NF" & you mean "well-designed"? See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Dec 15 '19 at 21:16
  • Please clarify via edits, not comments. Re "how do I normalize": Please post a new question, don't ask new questions in comments. But--That is just asking for us to rewrite your textbook with a bespoke tutorial & do your (home)work & you have shown no research or other effort. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific question re the first place you are stuck. – philipxy Dec 16 '19 at 04:25
  • Possible duplicate of [Two foreign keys referencing the same primary key](https://stackoverflow.com/q/11284428/3404097) – philipxy Dec 16 '19 at 04:50

1 Answers1

0

There is nothing inherently wrong with two FKs in the same table refer to the same parent / PK. I’d say the potential for non-normal-form is higher in general in this approach, but it boils down to your specific case.

Your approach of modeling both doctors and patients in a Person table is an OK design choice. In general for database design, it’s better to model day based on the role or use of the data, not just data domain. In other words, if patients and doctors play very different roles in your system, why not have separate Doctor and Patient tables? This would avoid issues like accidentally referencing a doctor where you should reference a patient or vice verse; or if different users / processes have different access permissions for doctor vs. patient data. Your current design is fine in terms of its normalization; but has a slightly odd smell in terms of overall data model.

Chris Johnson
  • 20,650
  • 6
  • 81
  • 80
  • My schema is Person table: PeronID, Name,Age,Address and Appointment table: AppID, Date, PatientID, New/GeneralPatient, DoctorID, DoctorCertification. The PersonID is PatientID for patients and DoctorID for doctors and one person can have many apointments. How do I normalize thsis? – Pratistha Kansakar Dec 16 '19 at 02:47