designing a database with type and sub type

Question

I am trying to build a publications database, and i want to develop a genre relation that has various (maybe look up tables) sub-genres attached. as an example, in the genre relation there would be a row for 'POETRY' but somehow i want to divide that into sub genres like 'PROSE', 'HAIKU' or 'FREE_VERSE'.

what is the best way to design such a thing? would i have a separate look-up table for each row in the genre relation for the various sub-genres? that doesn't seem very flexible. any input would be greatly appreciated.

Answer 1

This seems nearly identical to the following question, and my answer there should be detailed enough to explain the concept (and is just too much to copy and paste here).

Don't know how to transform variable entity into relational table

For your case specifically I would think it should work itself out to be something along the lines of:

• Publication table is the main entity with the common properties, a PK on PublicationID, and a PublicationTypeID field that FKs to PublicationType
• Lookup table for PublicationType that has a PK of PublicationTypeID. Entries would be the various genres.
• Sub-class tables for the genres, such as Poetry, Fiction, etc. Each sub-class table has a PK named PublicationID that also FKs back to the Publication table, and fields for properties that are specific to the particular genre.
• Where applicable, sub-class tables have sub-type field, such as PoetryTypeID and FKs to the PoetryType table.
• Lookup tables for sub-classes, one table per each sub-class type. The PK would be PoetryTypeID (e.g. Prose, Haiku, Free Verse, etc).

---

In light of additional details being made available that give context to the scope of the request (specifically the following comment on the question:

the data files ... are all in fact printed, no audio of image/video files. ... one sub-genre is sufficient, and ... it is possible to have just a genre without any sub sections..

), it seems clear now that the flexibility allowed for by my suggestion above is not needed as sub-types aren't really being stored. Instead, the request now seems to be for simply a means of categorization. For that, if you want a rigid structure of Genre / Sub-Genre such that:

• those are the only two levels, and
• you always know which level you are dealing with (in queries), as opposed to a dynamic structure where each Publication can be associated with a Genre / Sub-Genre at any level (1 through N) and you would need to query to get the heirarchy

then you can do something along the lines of:

Publication
-------------
PublicationID  INT           NOT NULL  PRIMARY KEY
SubGenreID     TINYINT       NOT NULL  FOREIGN KEY  REFERENCES(SubGenre.SubGenreID)
Title          VARCHAR(200)  NOT NULL
...


SubGenre
-------------
SubGenreID     TINYINT      NOT NULL  PRIMARY KEY
GenreID        TINYINT      NOT NULL  FOREIGN KEY  REFERENCES(Genre.GenreID)
Name           VARCHAR(50)  NOT NULL


Genre
-------------
GenreID        TINYINT      NOT NULL  PRIMARY KEY
Name           VARCHAR(50)  NOT NULL

In this model you know that what is in the Publication table is always the SubGenre, and it always has a parent Genre. You did say that it is possible to not have a SubGenre (though is that really the case?), you can use the same Name for both SubGenre and Genre (e.g. News -> News instead of Poetry -> Bad Teenage).

Or, if you want a dynamic structure of Genre / Sub-Genre such that:

• There can be just one level or any number of levels, and

• You can only easily tell if you are either at:

  • a top-level Genre (ParentID IS NULL), or
  • one or more levels deep in SubGenre-land (ParentID IS NOT NULL).

  Else you need to query to get the full hierarchy

then you can do something along the lines of:

Publication
-------------
PublicationID       INT           NOT NULL  PRIMARY KEY
PublicationTypeID   SMALLINT      NOT NULL  FOREIGN KEY
                                              REFERENCES(PublicationType.PublicationTypeID)
Title               VARCHAR(200)  NOT NULL
...


PublicationType
-------------
PublicationTypeID        SMALLINT     NOT NULL  PRIMARY KEY
ParentPublicationTypeID  SMALLINT     NULL      FOREIGN KEY  
                                              REFERENCES(PublicationType.PublicationTypeID)
Name                     VARCHAR(50)  NOT NULL

Please note that the ParentPublicationTypeID field is NULLable, which indicates a top-level item when it is NULL. This is the model that Walter Mitty suggested.

Answer 2

Perhaps you'll find the "Nested Set Model" for suitable for genres. Some advantages to this are:

1. You can sub-genre things as deeply as your heart desires.
2. You only need 1 table for defining Genres (not counting the associative table between Genre and Publication).

Here's an article that explains it: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

In your case, the tables would like something like so:

Some sample data:

For this sample data, "Green Eggs and Ham" are assigned the genres of "Children's", "Prose", and "Poetry". Even though the "Poetry" assignment isn't explicit in the PublicationGenre table, it can be inferred by the fact that "Prose" is a child of "Poetry". Similarly, "Joe's Haiku" is assigned the genres of "Haiku" and "Poetry".

What the "Nested Set Model" would look like for this sample data:

Answer 3

A solution like class-table-inheritance is probably overkill in this case. It sounds like you need the same data, in the same fields, for sub genres and genres. This can all be stored in one lookup table, the PublicationType table.

You need one more field in this table, to record the relationship between sub-genres and genres. I'll call this field ParentPublicationTypeId. The entry in the Haiku row would contain the PublicationTypeId for Poetry. This references a different row in the same table, but it's just another case of a foreign key. The buzzword is "reflexive relationships" if you want to find further reading.