I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have to insert a null foreign key field. To elaborate:
I have a table
Sense_LanguageSource which lists individual etymologies of entries in a dictionary. This has the following fields:
- LanguageSourceID (PK - Autonumber)
- SenseFK (FK - Number)
- LanguageCode (FK - Number)
- SourceWord (Short text)
- LanguageSourceType (Short text)
- IsWaseieigo (Yes/No)
LanguageCode field is the problematic case. It's used to list the language which the etymology derives from, and in this case it's a foreign key linking to a
LanguageCodeValue table, which contains both the ISO-639-2B code and the full name of the language.
However, some of the etymologies I'm going to insert into the database don't have LanguageCodes, so I would have to insert a 0 into that field. Initially, Access wouldn't let me do this, so I disabled Referential Integrity. Then when I reopened the database, Access said that the Database was compromised and needed to be repaired. So I think that I need an alternative solution to this.
I would make it less normalised and simply have a text field for the LanguageCode in there, but there are two tables that refer to the
LanguageCodeValue table, and it seems to make a lot of sense to have a table describing what each LanguageCode refers to. The solutions I can think of right now are:
Remove the relationship between the
LanguageCodefields and the
LanguageCodeValuetable (so that it's purely a reference table, and not linked,) and just use
SELECTqueries if I need to look-up the meaning of the LanguageCode.
Keep the link between the tables without Referential Integrity to allow null foreign keys, and hope that the database doesn't break down.
Insert a ""null"" value in the
LanguageCodeValuetable to refer to (by which I mean an actual record with a key value of 0 or similar, and blank values for the text fields), so that instead of entering a null foreign key, I can just link to a record that doesn't mean anything.
As I'm new to databases and not sure about best design practices, I would like to know which solution is most appropriate in my situation.