I'm designing a database structure wherein I like to apply the concept of inheritance. There's a base tables and derived tables in which the relationship is one-to-one. I'm quite new as of how could this be achieved and how the data are to be fed to the tables because I'm getting errors I can't solved.

I'm wondering if I'm on the right track doing this.

Here's the design view of the database:

enter image description here

I get the error of

You cannot add or change a record because a related record is required in the table **PatientRegular**

In which case the table I'm on currently working?

And the relationship view:

enter image description here

What is the right way to do that? I want to inherit all those similar fields to another table?


Either way, I cannot also insert new data in the base table (I must be in a wrong track)

enter image description here

  • 1
    Shouldn't those relationships be one to many as a patient could have multiple check-ups and operations? – James Anderson May 17 '14 at 12:33
  • @JamesAnderson Uhh can you elaborate more? Does that means that the derived tables will not have a primary key then? – mr5 May 17 '14 at 12:41
  • No they can and should have a Primary Key. What I mean is 1 Patient can have many operations. This means they could have many records in the operations table so the relationship should be 1 to many not 1 to 1. – James Anderson May 17 '14 at 12:43
  • @JamesAnderson Uhm do I need to create another field to refer, to make the one to many? – mr5 May 17 '14 at 12:51
  • Yes each table other than the patient table will need it's on ID column which will be the Primary key of that table. For example the Operation table would have OperationID. This keeps each operation record unique. You will join the tables on the PatientID still. – James Anderson May 17 '14 at 12:55

In this scenario, as James Anderson suggests, one-to-one relationships are not the way to do it. One patient may be checked up, operated on, discharged etc., more than once. The tables you have now should be renamed as follows and given their own unique ID primary keys to better represent what they are trying to model:

  • PatientRegular to Regular
  • PatientDischarge to Discharge
  • PatientOperate to Operation
  • PatientCheckup to Checkup

They should still retain their PatientID columns but those columns should become foreign key reference columns back to the PatientID column in the Patient table. In other words, you will have one-to-many relationships between:

  • Patient to Regular
  • Patient to Discharge
  • Patient to Operation
  • Patient to Checkup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.