designing a database with type and sub type 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.
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&#39;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.