Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?



share|improve this question
Your second approach is a very formal sub-typing schema. It is what you should have in a logical model. Your first approach is a fairly sensible compromise for simplicity and is what you'd likely have in a physical model. – Joel Brown Sep 18 '13 at 12:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.