There are some tables in my database that contain a foreign key field that is linked to another table. The problem is that the value for this field will not always be there, so sometimes I need to insert a null foreign key field. Working out:
I have a table
Sense_LanguageSource Here are listed individual etymologies of entries in a dictionary. This has the following fields:
- LanguageSourceID (PK – Autonumber)
- SenseFK (FK number)
- language code (FK number)
- SourceWord (short text)
- LanguageSourceType (short text)
- IsWaseieigo (yes / no)
LanguageCode Field is the problematic case. It is used to list the language from which the etymology is derived. In this case, it is a foreign key that is linked to a
LanguageCodeValue Table containing both the ISO-639-2B code and the full name of the language.
However, some of the etymologies that I will insert into the database have no LanguageCodes, so I would need to include a 0 in this field. Initially Access did not allow me to do that, so I disabled referential integrity. When I reopened the database, Access said the database was compromised and needed repair. So I think I need an alternative solution.
I would less normalize it and just have a text box for the LanguageCode in it, but there are two tables that refer to that
LanguageCodeValue It seems to make sense to have a table that describes what every LanguageCode refers to. The solutions that come to my mind are:
Remove the relationship between the
LanguageCode Fields and the
LanguageCodeValue table (so it's just a reference table and not linked) and just use it
SELECT asks if I need to look up the meaning of the LanguageCode.
Keep the join between the tables without referential integrity to allow null foreign keys, and hope that the database will not fail.
Insert a "null" value in this
LanguageCodeValue table to reference so that instead of entering a null foreign key, I can only reference a record that means nothing.
Since I have no previous experience with databases and I am not sure which design practices are best, I would like to know which solution suits my situation best.