I've seen a few instances of entities which have a field which can be either a reference to another entity or free text.
You typically represent this in the UI by having a dropdown widget which can optionally have text instead of one of the dropdown items selected.
Say you have a song entity which was recorded by an artist- the artist can be either a reference to an artist entity or a random string.
I'd usually represent this like:
create table artists ( artist_id primary key, [...] ); create table songs ( [...] artist_id null foreign key references artists(artist_id), artist_text null text, -- optionally add a constraint that checks that one of these fields -- is null, and the other is not );
If you are particularly averse to nulls, you might have:
create table songs ( song_id primary key, [...] ); create table known_artist_songs ( song_id primary key references songs(song_id), artist_id foreign key references artists(artist_id) ); create table unknown_artist_songs ( song_id primary key references songs(song_id), artist text );
, but it seems a bit overkill to me. You could also force the creation of an artist row in any case, but sometimes this doesn't seem appropriate.
Are there any better approaches?