From 1 to 0..1 - how to indicate FK?

Question

Let's say that I have a client table with a ratio of 1: 0..1 with another table, usually I have a Nullable FK in the client table pointing to another table.

However, let's say the number of additional optional pieces of data associated with the client is growing, and for the sake of arguments alone the number of tables is now 10. It would be preferable to use the same architecture so that there are 10 more columns in the client table, possibly null if additional data has not been stored or is it better if the FK points to a customer table from a child? This model seems more accurate, as I don’t have a ton of columns with a zero value, and I can expand the system gradually, if necessary, by simply adding new tables and a new FK column pointing to the client in the new table. The only drawback is that it appears (looking at db), that you can add more lines that violate the rule of relations 1: 0-1. However, my application will never insert an extra line.

The first method requires me to apply a new column at the end of the client table for each new table added to the system.

Which method is best in this scenario?

Answer 1

The answer is mechanically derived from the idea of ​​functional dependence.

For meaning to exist in one respect, it implies that meaning must exist in another. When this is true, there will be a foreign key restriction from the dependent table (first) to the independent table (last)

Another way to look at this is that a one-to-one relationship is really just a special case of a one to many relationship; only instead of many, you are only allowed one.

in SQL:

CREATE TABLE independent ( id INTEGER PRIMARY KEY ); CREATE TABLE dependent ( independent_id INTEGER UNIQUE NOT NULL FOREIGN KEY REFERENCES independent(id) ); 

As one for many, many have a foreign key for the one, but to turn many into one, just make it unique . It is usually convenient to express all this, making the foreign key column in the dependent relation the primary key for this relationship:

 CREATE TABLE dependent ( independent_id INTEGER PRIMARY KEY FOREIGN KEY REFERENCES independent(id) ); 

Edit: I noticed that your name is asking a different question than what your body seems to be asking. The above is responsible for the header.

From the point of view of database normalization, it is probably preferable to use several tables, as indicated above, in favor of attributes with a null value. Nulls is a kind of out-of-band way of saying that the value of a particular attribute is somehow “special”, but does not really provide any specific interpretation of what that might mean. Zero manager_id probably means something completely different from null birthdate , although they have the same sign.

Adding tables is by no means considered bad, from a strictly abstract or academic point; nor add attributes. The choice should always be based on what data you really need for modeling.

However, there are some very real practical reasons for using one or the other. The most obvious reason for performance is associated with the cost of using one or the other. When an optional value is usually used, the extra space used by the foreign key and the corresponding index does not provide itself well. Similarly, if an optional value is rarely used; it is more compact to put these values ​​in a different relation. Having the nullable attribute will consume space in the table, which is almost never used.

Computing, which basically requires actual data, and testing the performance of these (and possibly other) configurations to see which one works best.

Answer 2

Partial Answer:

Keep in mind that breaking a table of two with a ratio of 1-1 or 1-0..1 will always require additional joining between these tables.

If you often need to return data from both tables together, and these tables are heavily loaded, then "tons of NULL values" in a larger separate table will work better.