Hi!

If I have a field in a table, that has a foreign key but also allows
nulls, would it affect my queries, performance or data size in some
way? Specifically, if most values in the field are nulls?

My other option is to create a junction table between the two tables
and only insert row-pairs that are related, but this solution will lead
to a problem because it enables a many-to-many relation when all I want
is one-to-many (and usually no relation)...

Thanks in advance,
Ronald

If you have a junction table you can still implement a foreign key in the
way you want. Just create the primary key on the column that references the
child table, instead of creating a primary key on both the column that
references the cild table and the column that references the parent table,
as you would do with junction tables that implement a many-to-many
relationship.

Regarding performance, that is something you will have to test. In general I
don't think there will be much difference with one design or the other.

Show quoteHide quote

Of the two options, storing the foreign key in the table will result in the
least data storage, becuase only one key value per relationship, instead of
two foreign keys with a junction table. Also, related queries will require
fewer joins. However, this model is less flexible and would require revision
if there is later a need for a many to many relationship. For example, some
data models may start out with a many to one or one to one relationship
between customer and address, only to discover at a later point that there
is a need to track both current and previous addresses for the customer,
which requires a many to many relationship and thus a junction table.

Show quoteHide quote

I have used nullable foreign keys without any noticeable performance issues.
Just make sure that any programmers accessing the tables are aware of the
relationship and know how to build left outer joins.

Show quoteHide quote

Actually, you can create a one-many relationship with a junction table, just
create the junction table as you would normally do with foreign key
constraints on each column and place a primary key constraint across both
columns.  then place a unique constraint on the column that refers to the
"many" table.

If most of the rows are null, an index on the nullable column will have poor
selectivity.

Show quoteHide quote

3 Questions on db Theory/Design
Order by Case statement fails with Union Operator
restoring db without log
Upgrade from SQL Server 7.0 to SQL Server 2000
How to get the count of rows selected in a cursor