I am building a database to keep track of promotions. For the most part it is pretty straight forward the promotion table will contain the promo ID, name, and several dates including the start and end date of the promotion. In some situations, but not all the promotion may fall under the umbrella of another "parent" promotion. If this were a situation where there was a parent for every promotion I would create two tables (master and detail) and put data that differs from one "child" promotion to the next in the detail table. But since not every "child" has a parent I am wondering if I should just keep them all in one table and flag which ones are "parent" promotions.
If a promotion can only be associated with a single parent - then you could use a ParentPromoID column and populate that column with a reference to the parent promotion ID.
You can then find all parent promotions by querying the table for those promotions that don't have a ParentPromoID - and include child promotions by joining to the table again on the relationship from PromoID to ParentPromoID.
With this structure you can have a promotion that has multiple 'child' promotions - but never where you could have a child promotion related to multiple parent promotions.
If a promotion can be related to multiple parent promotions - then you would create a secondary table that implemented the many to many relationship with 2 columns: ParentPromoID and PromoID - where the combination of both columns make up the primary key. This would allow multiple parents to be related to a single promotion - and multiple promotions related to a single parent.
One more point to consider - can a child promotion be a parent to a different child promotion? If so - then you you have a hierarchy and need to consider which method best fits (hierarchy, adjacency list, other).