“Boys - to the left, girls - to the right”, or add the “Gender” field in the Oracle database

... there are three types of dragons: zero, imaginary and negative. All of them do not exist, but each type is in its own particular manner. The imaginary and zero dragons, called in the professional language imaginary and null, do not exist in a much less interesting way than the negative ones.

Stanislav Lem,

Cyberiad

Preamble

Constructive criticism of the article "Problems of merging records in a complex Oracle table" post. Let us try to analyze the methods for solving the problem of identifying the sex of a person based on his client data in the Oracle database.

Initial data

Suppose there is some experimental database on Oracle that contains customer data in the hotel.cards table. At a minimum, we have the last name (cards.last_name), the first name (cards.first_name) of the client ... and maybe even the middle name (cards.middle_name). Let's see what we can learn from this data.

middle name

The most unequivocal and trivial solution to identify the gender of our client is information about his patronymic. There are not so many suffixes of Russian middle names in Russian.



Actually, if all the patronymic names are entered into the base, then the field can be filled in a very trivial way.



But this method is not always applicable for several reasons:

1) the system was developed by a foreign customer, and the database scheme does not provide for patronymic names;

2) our foreign clients in foreign passports do not have the “Patronymic” field, and our operators do not specify this data with them.



For such cases, we still have the name and surname of the client.

')

Surname

Hours of joint meditation with Google led me to the following disappointing facts:

1) foreign surnames are usually unchanged in the masculine and feminine (English: Mr. and Mrs. Smidt, Ukrainian: Gregory and Maria Beloshtan, Ivan and Galina Krasnoshlapka);

2) the last letters of a foreign surname cannot unequivocally indicate the gender of its owner;

3) one of its parts may indicate to the floor of the owner of a compound last name.



In general, there are so many exceptions with the surnames that manual work is definitely not to be avoided. Naturally, our operators will do it, so that in the coming week do not expect invitations from them for tea with cookies.

Name

The most unambiguous parameter in our database for analyzing a person’s gender is his name. There are many sites with lists of male and female names, books with interpretations of male and female names can be found in paper and electronic form, but ... We, due to our laziness, should find either a ready-made database of names with gender (such by the way are sold), or search this information is in a form suitable for machine processing. All we need is to our sample.

select distinct hc.first_name from hotel.cards hc;



add the gender field and fill it with the values ​​"male / female". Meditations with Google on reference books and databases of names, various api to social networks and points of sale of various databases suddenly stopped when I came across the service “i-gender.com” . A very convenient API, recognition of names in Cyrillic and transliteration, as well as short testing of the veracity of its results convinced me that it is possible to transfer the routine work of our operators on the shoulders of this service so as not to distract them from the process of treating me with the liver.



The service is very unpretentious, demanding from us an http request of the form name = Innocent. In response, we get a string like {"gender": "male", "confidence": "100"}. True, the request must be made using the POST method. For curl, this is not a problem; it is easy to assign an analysis of the answer to a simple bash script. It remains to perform a series of routine gestures and to persuade the operators to review the results of work for possible mistakes of the i-gender service . Curious please under spoiler.







When the data is connected to the database, they need to be subjected to preliminary verification using human labor. You can then integrate them into the existing schema, expanding the existing tables, creating new ones associated with views and triggers. But this has little to do with the topic of our article.

Conclusion

Using only the last name, first name and patronymic of our client, we cannot solve the task set automatically. But either purchased databases and third-party samples, or services like www.i-gender.com will help speed up and automate our task .