I'm new, recent softdev grad and I'm working on a project with a weird relationship. Trying to figure out the best way to structure a couple of tables. I feel like it's pretty easy but I can't quite wrap my head around it. Any help would be appreciated. This structure will be used to store classical music.
So I have four things, a songs, works, albums and playlists.
A song is the base.
A work is made up of multiple songs.
An album is made up of multiple songs and/or a work.
A playlist is one or a combination of any of the previous.
I keep trying to do it with a recursive M2M with a song grouping type or something.
What database platform are you going to use? I can't imagine that you would overload it.
As for entity attributes, they can all be placed in the same table, allowing NULL values when they are inappropriate for the entity type, or they can be placed in distinct subtables with joins to their parent entity record.
If it's not practically useful, then it's practically useless.