create additional unique constraints

[patrickakk]

currently it's possible to enter duplicate data in the admin area. the following items should be unique: cities, countries, courses: on institution_id & course name, faq_questions.name, invite_translations.language_id, languages

create these rules in the database and application.

fix conflicts when they occur.

[patrickakk]

A unique for the table courses on institution_id & course name is required for the search bar (#62) , since the lookup depends on this being unique.

[patrickakk]

-removed content of this comment-

reason: moved all conflicts to a list

[patrickakk]

unique for the table courses on institution_id & course name implemented in local test database. 58 conflicts found. spreadsheet with proposed solutions is available.

[patrickakk]

waiting for decisions in next meeting

[patrickakk]

The spreadsheet with suggested solutions to fix the conflicts can be found here:
https://docs.google.com/spreadsheets/d/1V6rUDUKE34EhwGgMYg0dylOUwJB0nlLP6wlSnKgeeZY/edit?usp=sharing

[patrickakk]

Waiting for reaction on proposed solutions in spreadsheet.

[patrickakk]

@PixlTracer @IvdL22

Update:
Based on the fact that the Education Type is also shown in the list view, I've updated the unique rule, so that now the combination of Course Name + Institution + Education Type needs to be unique. This avoids adding a Education Type in the name and duplicating data.

In the spreadsheet linked above, I've marked the lines we can ignore in grey/black. As you can see in column G, only 21 courses are left, that's were we need to take a decision about.

Can you tell if the proposed changes in column H can be made?

[PixlTracer]

Hi Patrick
I finally managed to go through the list; I have added my comments in column I.

while checking the courses it came to my mind, that this might happen again in the future:
some courses taught in different cities all over Europe might have the same name (e.g. introduction to digital humanities), some courses taught at a certain university might have the same name too, e.g. Digital Humanities (in both BA's & MA's);

Hence, the following idea came to my mind:
when using the search bar, a user is entering certain keywords - why not output a list (similar to when applying filters) of all the courses that match the keyword? so we don't need to check the course-names for their uniqueness...

very much looking forward to the deployment of the new feature!

[patrickakk]

@PixlTracer Thanks for taking a look at the list. I'd like to divide you comment in thee parts.
(cc @IvdL22 )

while checking the courses it came to my mind, that this might happen again in the future:
some courses taught in different cities all over Europe might have the same name (e.g. introduction to digital humanities), some courses taught at a certain university might have the same name too, e.g. Digital Humanities (in both BA's & MA's);

so we don't need to check the course-names for their uniqueness

In the future we don't have to do that. That is what the implementation of the combination of Course Name + Institution + Education Type being unique means. As soon as this is in production, the application will do that for us.
What we are doing right now, is cleaning up after 9 years running without this validation rule having implemented.

Hence, the following idea came to my mind:
when using the search bar, a user is entering certain keywords - why not output a list (similar to when applying filters) of all the courses that match the keyword?

Can we continue the talk about the search bar in the corresponding issue? Here:
#62 (comment)

I finally managed to go through the list; I have added my comments in column I.

Thanks for your suggestions, I have a few remarks.

• In the last meeting we agreed to add “(old)” to older courses which are duplicate. Now you suggested to add "(deprecated)" instead. Do we all agree on this?

• In other case you suggested to include the education type into the course name. This would be duplicating data, since the education type is already stored in a separate field and shown in a separate column in the main page?
  So I would suggest not add the education types in the course name.

• In a few cases you rewrote the course name? My idea was that we as administrators usually don't change data and leave it the way it's entered by the users. The only exception would be adding old/deprecated to implement the additional validation rules. We could leave the course name unchanged?

Link to the spreadsheet (again): https://docs.google.com/spreadsheets/d/1V6rUDUKE34EhwGgMYg0dylOUwJB0nlLP6wlSnKgeeZY/edit?usp=sharing

Based on your suggestions, I've made a new proposal in column J. Can it be changed like that?

[patrickakk]

As discussed in the meeting today, the proposal in column J is agreed and the changes can be made.

[PixlTracer]

thank you!
should we assign the label 'done'?

[patrickakk]

@PixlTracer

Great that you're happy with the implemented solution.

This was only one validation rule. (Which was prioritized, because it was needed earlier)

I'll move the issue to a next milestone and then the following validation rules can be implemented;
Unique on:

• cities
• countries
• faq_questions.name,
• invite_translations.language_id,
• languages

[patrickakk]

No time available for this now, moved to November milestone.

It's still important because it contributes to the quality of the data.

[patrickakk]

• invite_translations - language_id
• faq_questions - question
• countries
• cities
• languages