5 Replies - 7551 Views - Last Post: 20 May 2013 - 08:22 AM

#1 James1992   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 132
  • Joined: 30-October 12

How do I create a table with a 0-1 relationship?

Posted 17 May 2013 - 04:44 AM

Okay my title might be a little ambiguous, so I'll expand on it a little.

I am currently revising for a test I have in a few weeks, and this is a question on a past paper.

I have the following class diagram. Attached Image

The actual question is:

"the following class diagram represents the data that needs to be stored in a doctors' surgery information system. An appointment can be made by a patient with one doctor at a specific date and time. Appointments can be linked together by the 'follows' association. The 'patientName' and 'name' attributes are unique. No other attributes or combination of attributes are unique.

Before I write the SQL to create the tables, I must first map out the diagram into a table design, displaying the column names, column types, whether the column can be null and finally whether it is a primary/foreign key. Tables must also show that if a patient is removed from the system, the patient's appointments are also removed from the system; and a doctor can only be removed from the system if the doctor has no appointments.

To be exact, these are the tables I currently have:

Attached Image
Attached Image

I am unsure whether my SQL table for the Appointments correctly uses the ON DELETE CASCADE to represent "a doctor can only be removed from the system if the doctor has no appointments"

As for the 0..1 relationship on Appoinments, I believe I must create another table and use a foreign key that links back to appointment. However, I'm not so sure how to overcome this. If you're not already bored of my question, I would much appreciate any guidance on how to tackle this issue. If you believe my SQL to be wrong, please point it out. Any advice is welcomed.

Thank you in advance.

Sorry if my question is a little confusing.

I hope this doesn't come under the statement "we wont do your homework for you". I'm not after that.

Is This A Good Question/Topic? 0
  • +

Replies To: How do I create a table with a 0-1 relationship?

#2 andrewsw   User is offline

  • blow up my boots
  • member icon

Reputation: 6549
  • View blog
  • Posts: 26,550
  • Joined: 12-December 12

Re: How do I create a table with a 0-1 relationship?

Posted 17 May 2013 - 05:14 AM

The term is ON DELETE CASCADE not ON CASCADE DELETE.

My interpretation is that you should explicitly not use ON DELETE CASCADE, as this would mean that the Doctor could be deleted and all his appointments would disappear, leaving angry patients in the waiting room. Without ODC (cf OCD) it would not be possible to remove the doctor if he still had appointments.

I haven't thought too much about follows but I assume the Appointments table should have a separate primary key, and this should be referred back to as a new Follows field using a self-join.

This post has been edited by andrewsw: 17 May 2013 - 05:18 AM

Was This Post Helpful? 1
  • +
  • -

#3 James1992   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 132
  • Joined: 30-October 12

Re: How do I create a table with a 0-1 relationship?

Posted 17 May 2013 - 05:30 AM

Thanks for spotting the ON DELETE CASCADE error :)

I believe I understand your comment about not using ODC on the docName foreign key. Using ODC will delete everything connected. Not using it will prevent you deleting the doctor foreign key if he still has appointments. Again, thanks for helping me on that section as well!

I will look into self-joins.

Thanks Andrew, +1 rep for you!
Was This Post Helpful? 0
  • +
  • -

#4 James1992   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 132
  • Joined: 30-October 12

Re: How do I create a table with a 0-1 relationship?

Posted 17 May 2013 - 07:03 AM

Andrew, would you say this solves the follows?

Attached Image

Because "previousAppointment" and "nextAppointment" are referencing the same attribute, is it regarded as better practice to write the FOREIGN KEY as

 FOREIGN KEY (previousAppointment, nextAppointment) REFERENCES Appointment(ID);



I assume both do the same thing..

Thanks.

This post has been edited by James1992: 17 May 2013 - 07:07 AM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • blow up my boots
  • member icon

Reputation: 6549
  • View blog
  • Posts: 26,550
  • Joined: 12-December 12

Re: How do I create a table with a 0-1 relationship?

Posted 17 May 2013 - 09:07 AM

I'm not really sure about this Follows business and you'll need to make your own judgement, unless someone else offers advice.

There is the question as it is stated, and then there is how this might work in practice. In practice (excusing the pun :)) I don't think these fields would be necessary as the appointments schedule could be determined using queries, based on the Doctor's name, and appointment dates and times.

Focussing on the specific question though, it could either be a single field Follows in the Appointments table, using a self-join on an additional primary key-field. (But then finding the next appointment would be fiddly, and keeping the Follows field up-to-date would also be a hassle.) Or it could be a completely new table with, as you've outlined, previous and next appointments.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#6 James1992   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 132
  • Joined: 30-October 12

Re: How do I create a table with a 0-1 relationship?

Posted 20 May 2013 - 08:22 AM

I guess with it's a 0..1 I could add another attribute within the Appointment table. If it was a many-to-many then I reckon I would certainly have to create a separate table.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1