2

I'm designing a database schema for a music platform.

Some of the entities as follows:

  • track
  • album
  • band
  • artist

A "track" can have an "artist". It can also have a "band" which makes the artist FK null.

Same goes with "album", an "album" can have an "artist" but if the "album" is from a "band" then the artist FK will be null. That's because an "album" by a "band" can't also have a single "artist" as well.

The two(or three) FKs will never be filled together and only one of them must be filled for each record.

Is this a good design practice?

Thanks

1

Good design practice is to implement and enforce the rules of the problem domain. So if your rules lead to this design then that's what you've got. Having mutually exclusive values is not uncommon.

They can be implemented in different ways. One is as you describe. Another is to use table inheritance. With this you would have tables track, artist and band as you do currently but also band_track and artist_track. These will have only the foreign keys specific to themselves, defined as not null. The problem then becomes one of enforcing exclusion between band_track and artist_track. Personally, I don't think this approach actually improves our position much.

For my two cents I'd go with what you have. Declare CHECK constraints to prevent illegal combinations within a row (or use triggers if you absolutely must).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.