CODEWITHSUNDEEP
database-designentity-relationshipdata-modelingsubtypesupertype
Nik Bhardwaj
Nik Bhardwaj

Reputation: 39

Super Type / Subtype : Data modeling

I have a Supertype / Subtype relationship in which one of the subtypes doesn't have any attributes, so I am unsure on how this would be modelled. I have a supertype called Tracks in which a track can be one of 3 subtypes, Audio , Video , Text. Both the audio and video subtypes have specific attributes to them, so it makes sense to put them in a separate table. The Text subtype has no additional metadata, so should I capture this in a separate table?

Upvotes: 3

Views: 2794

Answers (2)

Ahmed Tanvir
Ahmed Tanvir

Reputation: 197

Just add a separate column in the track table called tracktype. In this column store the type of track whither it is audio..text. The track table should contain all types of track info with the attributes common to all type and now for each type you need separate table. All of them will have their own identifier with other attributes and the identifier should be both primary key and foreign key from the track table. You can avoid creating text table as it doesn't have any attribute. But if any other table specially depends on the text table then you can create a separate table for text and put only one attribute in that table which is the identifier of the text entity.

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

If you have a type discriminator1 in your supertype table, then you don't need a subtype table at all - the subtype would be identified by type discriminator and all of its fields are already in the base table.

Otherwise, the subtype table would just duplicate the super table's PK (and no additional fields).

Or, you could consider "all types in the same table" strategy for implementing inheritance.

1 Simply a field that identifies the type of its row - typically an integer (or enum if DBMS supports it) that takes its values from a "well-known" and documented set.

Upvotes: 2

Related Questions