php - Database design on uncertainty classification

Question

Current database design:
School table: school_id, name
User table: uid, school_id, school_name

Brief description of the problem:
For returnee business, there needs to be a study abroad school field in the user field. However, there are so many schools around the world that cannot be included in the school table, resulting in many users reporting that their school is not available. A school_name field is added to the user table to allow users to fill in the school name themselves when there is no school to choose from. However, schools can have many names, such as Peking University, Peking University, etc., resulting in duplication of schools. Another problem is that my school exists in the school table, but I filled in the name of the school with a similar name.

Requirement description:
Currently there is a requirement for same school recommendation. We only use the school_id field for recommendation, but this will cause the problem of incomplete recommendation data.

I would like to ask if there are any good design solutions for a business like this (a situation where the number of categories is large and the category names are diverse)?

Answer 1

1. Use select to provide users with choices. If not, users can click on other checkboxes and fill in customized school information.
2. Use autocomplete to operate, that is, when the user fills in the corresponding information in the text box, the text provides the corresponding school information for the user to select. If not, it is customized college information.
3. This needs to be processed in the background, through a Zhang table school, add a field, which is other descriptive words of this school. After the user fills in, he will continue to query the background school and other descriptive words to match, thereby reducing field problems