Implementing a one to zero or one relationship in SQL

I want a serious piece of DB theory

Modern relational theory rejects nulls, which would seem to immediately invalidate your option 1. However, this strawman may be eliminated by replacing the default null with a default value e.g. a 'dummy' customer created solely to explicitly model the "is not a customer" correspondence.

I think your option 2 is the most theoretically sound because, unlike the modified option 1, the relations can be in sixth normal form (6NF), being a projection-join normal form and the highest normal form attainable.

I have also heard of a design rule of thumb that states that a relation should model EITHER an entity OR the relationship between entities but never both, which seems sensible to me. Again, this would favour option 2. However, I heard of this rule of thumb many years ago, don't recall where and can offer no serious theoretical basis (other than 6NF as mentioned above).

You have been given the correct answer in part. The real answer comes from your data model and how it got normalized. A key is how you get to the relationship:

• The customers table consists of a number of fields considered for the users table which belong to the the customer concept, and are null unless the user is also a customer (sub-type of user). In this case the customers table inherits the primary key from the users table. (It is possible to multiple sub-types which may or may not overlap.)

• The customers table consists of a number of fields related to the customer concept, but not necessarily the user concept. The customer is a strong table and is not dependent on the user concept. (Removing the users table would not significantly impact the design of the customers table.) In this case the customers table gets its own primary key.

What you are have is a special case of optional one to many relationship where the upper bound is 1. Consider it from both sides: Would it be possible for one user to have multiple customers, or one customer to have multiple users? If so, you will need to remodel your data.

Adding the user-id foreign key to the customers table could be considered a better choice as correctly maps the one to many (upper limit 1) relationship and avoids a nullable field. To enforce the upper limit the foreign key index needs to be unique. This will occur automatically if the primary key is the user-id.

Adding the customer-id as an optional foreign key to the users table enforces the upper limit of 1 in the relationship but reverses the dependency.