0

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 is_paid is 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

Another example:

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 is_married is 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?

  • What's appropriate will depend on what the software is ultimately meant to do. Both examples are appropriate for different situations. – whatsisname Jul 11 '16 at 6:16
2

It depends is the only real answer here. The reason you ask this question and you are in doubt is because of the fact that you are unclear about the specs to build.

Example questions you should ask on orders example.

Initial client / product owner request:

"We want to see if the orders are paid in the orders screen."

Your questions:

Cool, sure we can do that. Do all orders get paid at once?

Client: Yes because we use PayPal which does not allow to change the amount so it's paid or unpaid.

OR Client: No, sometimes we deliver in separate shipments and they will only pay the amount delivered.

OR Client: No, it depends on our invoicing system here.

You: Ah so actually they pay the invoice, not the order?

Etcetera etcetera. It's not a technical issue, it's a requirements issue. And you cannot solve unclear requirements by technical fixes. (You might know the requirements swing diagram: http://www.tamingdata.com/wp-content/uploads/2010/07/tree-swing-project-management-large.png).

1

You are not thinking too much - your questions are very relevant for the design. But they are not technical questions, they are questions about the requirements. Should your system support payment plans or polygamous marriages? These are not technical questions, these are business decision.

As for spouses though it is obvious you have to support unmarried employees, so you need spouse_first_name etc. in a separate table with a foreign key to the Employee table. (You can prevent polygamy by having a unique constraint on the foreign key, if you so desire.) Of course this may have problems if two employees are married to each other - again this depends on the requirements.

When you have decided on a business model, then you can design the database. If some entities have a one-to-one relation, they should be in a single table, and if they have one-to-many, they should be in multiple tables. (One-to-zero-or-one as for the spouses is a special case of one-to-many.)

Database design should not follow separation of concerns in the usual sense though. You may have employees shoe size and favorite animal in the same table, even though they are not related, as long as the customer have exactly one of each.

As for has_first_name, has_spouse etc.: You don't need fields like these. If a single field is optional, then you use NULL to indicate the missing value. A flag does not provide any additional information, and you run the risk of inconsistent data (e.g. what if there is a first name but false in has_first_name?).

If it is multiple values which are optional as a group (like 'spouse_first_name', 'spouse_last_name') then it should be a separate entity (the Spouse-table), so again a separate flag is not necessary.

1

In layman's terms:

The whole point of relational databases is normalization.

Normalization means separating tables into different tables in order to avoid udpate anomalies and to speed up look ups, reduce redundancy etc.

So your question really is "should a database be normalized?" and the answer is "of course". Obviously there would be exceptions in a per-table basis where you can decide to denormalize for performance's sake.

Normalization dictates that a non-key column should describe the key, the whole key and nothing but the key. So you should avoid things like having the name of the store a product was bought from in the product table.

In the case of the tables you give as examples, I'd put them in a separate table. There's no one-to-one relationship in a relational database, but you can simulate one by adding the correct PK in the child table so that it would be imposible to insert a second spouse to the same employee, or a second payment to an order if that's the business rule. But a more sensible solution would be to add dates to the spouse table so a history of spouses is stored but you could always find the current one, and have multiple payments to an order.

In the other hand, hasSpouse is a calculated field indicating whether or not the spouse-related columns are populated or not. Calculated fields don't comply with normal forms and should only be used in summary tables (which don't abide by normalization rules since they are not transactional). But in this case it's worse because it's not a total summing some other columns but an indicator that could be replaced by simply asking whether a a column is null or not.

0

I am not sure what purpose requires the has_xxx fields. It is common to use nullable columns where the column may not have a value. You will need to do this anyway for the columns corresponding the the has_xxx fields. If the value is not NULL then has_xxx had better be true, and if the value is NULL, then has_xxs had better be false. This makes the has_xxx columns derivable, so they should be normalized out.

Some interfaces which return primitive values may have problems with NULL value. They may return 0 for numeric values, and and empty string for string and related types of values. They may have a related IS_NULL value that can be queried or set in DML. However, these values don't require corresponding columns in the database.

Some modelling purists would disallow NULL values in the database, but this would require a separate table for every set of nullable values. This is useful conceptually. However, I have yet to work on a system where I have seen the physical (database) implementation normalized to that extent.

In your model, the paid-date and paid-amount should have integrity constrains requiring that they both be NULL or neither NULL. It is common to handle payments in a separate table. This would have a join table to the orders(s) paid by that payment. The join table might have an amount column indicating how much of the order total was paid by that payment.

Your employee example is close to what I have seen implemented. Instead of a spouses table, you may want a dependents table. The dependents table could include spouces, children, parents, and possibly other types of dependents. Alternatively. both personal data for employees and dependents could be in a single people table with a circular join table. Data specific to employees would remain in an employees table to which the people table would have an optional 1 to 1 (or 1 to (0,1) relationship.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.