You don't know what people are using your database for
Please indulge me in another post which is only tangentially connected with SQLite. But I found this incident surprising and not a bit alarming. If I get complaints (none so far) I'll stop.
Service from Birmingham to Majorca took off with less thrust because pilot thought it was 1,200kg lighter [...]
An update to the airline’s reservation system while its planes were grounded due to the coronavirus pandemic led to 38 passengers on the flight being allocated a child’s “standard weight” of 35kg as opposed to the adult figure of 69kg. [...]
Investigators described the glitch as “a simple flaw” in an IT system. It was programmed in an unnamed foreign country where the title “Miss” is used for a child and “Ms” for an adult female.
So there you are, writing your database which takes passenger names. You include a title field. Nobody cares about that, right ? I mean, if my first name is Mark it doesn't matter if the contents are wrong. No need to sanity-check against gender or name fields.
Next time I'm writing a database for a customer, should I ask for all the purposes each field is used for ?
the purposes each field is used for
does NOT divulge the purposes for which the field might be used for in the future.
Storing the title, e.g. Miss, MS etc is data: this activity belongs to the data tier of the application.
Equating Miss to child and MS to adult female is business/application logic: this belongs to the business logic of the application.
The data tier cannot anticipate the business logic tier, let alone the business logic of the future; with the business logic, garbage in, garbage out applies very succinctly.
How do you store the field SURNAME in a database? And, what does it mean? While thinking of an answer, imagine this scenario: a female gets married and adopts her husband's surname, gets divorced and reverts to her maiden surname, gets re-married and adopts her second husband's surname, gets divorced and reverts to her maiden surname, emigrates and finds her surname has undesirable connotations and adopts another surname etc. etc.
In SQLite, any comments which are inside of the list of fields in the CREATE TABLE statement (or any other CREATE statement, I think) will be saved with the database. (Comments after the semicolon will not be saved.)
But, yes, to avoid such problems, you should ask what the fields are used for, and ensure they are not used badly. The description here is a bad idea, I think. There should be a separate field for the passenger's mass; the title field should only be used for display and no other purposes (although not necessarily only direct; if the software needs to deal with gender pronouns, it can use it to display that too, although that might be unnecessary).
I do know how weight and balance calculations for aircraft work, since I have programmed such a thing before (although it didn't use a database; it was simply a data file with the parameters for the specific type of aircraft in use).
There should be a separate field for the passenger's mass
Haha, is "you must weigh yourself to complete user registration" the next evolution of captcha?
Giving users control over this seems almost worse from an aviation engineering perspective, especially if "a simple flaw in an IT system" happens to allow negative numbers to make it into the database XD