Do you preferer null or default values when forced to, for meaningful necessary fields?

Question

Let's consider a scenario in which a user registers to a certain service which needs his gender, birthday and location to function properly.

Sometimes we want to try registration with just email then ask later for the other details in order to increase conversion rate and not to annoy the users.

In this case there are two solutions that I can think of:

1. make birthday, gender and place as nullable fields in database (place_id is referencing primary key in places table, so it will be a nullable index) and set them as null

2. just put some default value (ex always male, or another gender not set, and birthday now, or create a fictious place with certain id that internally we know that means not set and use it as default)

The risk with solution 1 that I can think of is that other parts of application (ex javascript frontend) may run into errors if not having very detailed validation. For example if we show the location in map and expect always to have place id but that is nullable an error may happen.

The second solution also looks "shady" to me, may have unexpected results.

So that's why I am here to ask you in case of suggestions for a better solution? Thanks in advance

Answer 1

Option 2 is just wrong in any scenario, you will not be able to tell whether you have a meaningful value or the visitor just never bothered to provide an answer. Null values are fine for unspecified data. If you offer a picklist you will likely have an enum behind it and you should have a default that means "not selected".

"Some software layer may get in trouble when it encounters null values" is an odd statement. Any software dealing with input should not be stupid of course, it must know about your data model.

Answer 2

Neither option works. If these are truly "meaningful necessary fields", then

• null is not an option. (If you can tolerate the absence of that value, then it's not necessary.)

• Default values are not an option. (No default value can be meaningful. At best, it can mean "the user didn't enter info"...and if you can tolerate that, then see the previous bullet point.)

Before considering either, i would rethink the requirements and the design. You're trying to violate your constraints here. If you need these fields, then it should not be possible to create a record without them.

If you're collecting this info from the user and accounting for the user entering a little info here and a little there, then maybe have a separate table where the incomplete users go. They only go into the real table once they're valid/complete.

(Personally, though, i'd just require that all the info be filled in at once. Even if you want to track abandonment of the registration process, half-users aren't of much use.)