Python - SQl query

[Bassmansam]

Hi All

I’ve beeb making a doctor/patient appointment booking app. I have an appointment table with patient/doctor id as foreign keys, i have an admin screen where appointment details can be entered (date, time, doctor name).

I will have a drop down to display the doctor names populated with a select statement. I can’t figure out how this will work if there are two doctors with the same name when doing the sql insert( insert doctor id into appointments where doctor name = doctor id)

this is a personal project as i’ve recently finished a udemy python course fyi

 

[Blueball]

Every Doctor has their own, unique 7 digit GMC registration number.

https://www.gmc-uk.org/registration-and-licensing/the-medical-register

 

[Bassmansam]

Every Doctor has their own, unique 7 digit GMC registration number.

https://www.gmc-uk.org/registration-and-licensing/the-medical-register

How would that work selecting a dr name from a list if are duplicate names?

 

[Blueball]

How would that work selecting a dr name from a list if are duplicate names?

Show the last 3 or 4 digits of their GMC Reg Number and differentiate on that.

 

[Bassmansam]

Show the last 3 or 4 digits of their GMC Reg Number and differentiate on that.

Ah right yeah that makes sense thank you

 

[andy o]

Natural keys are almost always a bad idea. Best to use either a guid ( so you can know the key in advance of insert ) or a self incrementing int.
A Dropdownlist Option can have an id attribute. Use that to hold your id for the doctor and show the name as content.
Doesn't then matter how many Dr Smiths you have.

 

[touch]

I agree with @andy o
You're populating the dropdown from a select statement, so just change that to include doctor id as well as doctor name. The id goes into the 'value' attribute of the select list items and the name goes into the 'text' attribute. You don't need to worry about matching up the name the user selected, you have the selected id instead which you can just pop straight into the appointment table.

But you'll also need to include @Blueball's suggestion too. Even though the code will work perfectly fine with multiple names the same because it's using ids rather than names, the user won't know which is the correct item to pick from the dropdown list if there's 2 the same. So I would include the name and partial reg number in the display but stick to using the id for the actual values selected.

 

[Bassmansam]

Natural keys are almost always a bad idea. Best to use either a guid ( so you can know the key in advance of insert ) or a self incrementing int.
A Dropdownlist Option can have an id attribute. Use that to hold your id for the doctor and show the name as content.
Doesn't then matter how many Dr Smiths you have.

I do use an auto incrementing int for the primary key

 

[visibleman]

I do use an auto incrementing int for the primary key

Use that as @touch mentions, both populating your doctor selector/dropdown as well as reference against appointment records.

 

[gpuerrilla]

Good tips guys, reminds me of some of the systems you inherit or work on and have to implement after.