Should database tables be separated by concern?

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).

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.

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.

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.