Take the 2-minute tour ×
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?

Cheers,

Álex

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

 
discard

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

Browse other questions tagged or ask your own question.