DB Design: Favor Abstraction or Foreign-Key Constraints ?

Question

Say we have this scenario:

Artist ==< Album ==< Track
//ie, One Artist can have many albums, and one album can have many tracks

In this case, all 3 entities have basically the same fields:

• ID
• Name
• A foreign of the one-many relationship to the corresponding children (Artist to Album and Album to Track

---

A typical solution to the provided solution would be three tables, with the same fields (ArtistID, AlbumID etc...) and foreign key constraints in the one-many relationship field.

But, can we in this case, incorporate a form of inheritance to avoid the repetition of the same field ? I'm talking something of the sort:

Table: EntityType(EntityTypeID, EntityName)
       This table would hold 3 entities (1. Artist, 2. Album, 3. Track)

Table: Entities(EntityID, Name, RelField, EntityTypeID)
       This table will hold the name of the entity (like the name of 
       an artist for example), the one-many field (foreign-key
       of EntityID) and EntityTypeID holding 1 for Artist, 2 for Album 
       and so on.

---

What do you think about the above design? Does it make sense to incorporate "OOP concepts" in this DB scenario?

And finally, would you prefer having the foreign-key constraints of the first scenario or the more generic (with the risk of linking an artist with a Track for example, since there is no check to see the inputter foreign-key value is really of an album) approach?

..btw, come to think of it, I think you can actually check if an inputted value of the RelField of an Artist corresponds to an Album, with triggers maybe?

Answer 1

There's very little chance that the additional fields that will inevitably accumulate on the various entities will be as obliging. Nothing to be gained by not reflecting reality in a reasonably close fashion.

I don't imagine you'd even likely conflate these entities in your regular OO design.

This reminds me (but only slightly) of an attempt I saw once to implement everything in a single table (named "Entity") with another table (named "Attributes") and a junction table between them.

Answer 2

I have recently seen this very idea of abstraction implemented consistenly, and the application and its database became a monster to maintain and troubleshoot. I will stay away from this technique. The simpler, the better, is my mantra.

Answer 3

I agree with le dorfier, you might get some reuse out of the notion of a base entity (ID, Name) but beyond that point the concepts of Artist, Album, and Track will diverge.

And a more realistic model would probably have to deal with the fact that multiple artists may contribute to a single track on an album...

Answer 4

By stucking all three together, you make your queries less readble (unless you then decompose the three categories as views) and you make searching and indexing more difficult.

Plus, at some point you'll want to add attributes to one category, which aren't attributes for the others. Sticking all three together gives you no room for change without ripping out chunks of your system.

Don't get so clever you trip yourself up.

Answer 5

The only advantage I can see to doing it in your OOP way is if there are other element types added in future (i.e., other than artist, album and track). In that case, you wouldn't need a schema change.

However, I'd tend to opt for the non-OOP way and just change the schema in that case. Some problems you have with the OOP solution are:

• what if you want to add the birthdate of artist?
• what if you want to store duration of albums and tracks?
• what if the want to store track type?

Basically, what if you want to store something that's psecific only to one or two of the element types?

Answer 6

If you're in to this sort of thing, then take a look at table inheritance in PostgreSQL.

create table Artist (id integer not null primary key, name varchar(50));
create table Album (parent integer foreign key (id) references Artist) inherits (Artist);
create table Track (parent integer foreign key (id) references Album) inherits (Artist);