My question is related to when should a database table be broken into multiple tables with relations? However, I have a clearer one-to-one relationship than in that question.
If I was designing the following table from scratch should it be in one table or two?
ORDERS id date total_amount is_paid paid_date paid_amount has_discrepancy discrepancy_reason
An order is never paid at its time of creation. It may or may not be paid later. I've been told that since it has a one-to-one correspondence it should always be in a single table. If
FALSE then the remaining fields are always
NULL. However, the following design makes more sense to me:
ORDERS id date total_amount PAYMENTS id order_id paid_date paid_amount has_discrepancy discrepancy_reason
EMPLOYEES id first_name last_name date_of_birth is_married spouse_first_name spouse_last_name spouse_date_of_birth
To me this seems like it's the same as the first example where if
FALSE the remaining fields are
NULL and I think it should be broken down like this:
EMPLOYEES id first_name last_name date_of_birth SPOUSES id married_to_employee_id spouse_first_name spouse_last_name spouse_date_of_birth
The first design in each case assumes that a one-to-one relationship is natural. However, payment plans with multiple payments are not unheard of, nor are polygamous marriages. Am I just thinking about this too much? Should I just do the task at hand or should I think about whether the data truly belongs in the same table or not?
I've been told that you wouldn't store a person's name in a separate table just for names and that the same principle applies here, but the difference is that you wouldn't design your table like this:
EMPLOYEES id has_first_name first_name has_last_name last_name has_date_of_birth date_of_birth
If you have a column that only tells you whether other columns are populated or not, isn't it better to store those values in a separate table? Or is it better to simplify the code by only having to deal with one table?