One-to-many relation between A and B, but A is divided into multiple tables

Phoenix Forum Questions / Help
, ,
1

I feel I might be at the verge of making a mess of my data model.

I have drafts that a user can publish. This results in a publication. You can see a publication a snapshot of the draft. However, one draft may also be published multiple times, or not at all. So far so good.

However, I now want to make several types of drafts. Each can be published. There is only one type of publication. At time of publishing, the draft is translated into a valid publication structure.

The problem is that each publication may only have one draft. So not one of each kind, but one in total. I attempted to draw this in the picture, using the long vertical double line.

I reckon I have to either accommodate all types of drafts in one drafts table, but that, I think, wouldn’t be ideal because the columns differ a fair bit between the kinds of drafts. I have some other ideas, but I don’t know how to verify those ideas because I don’t know what to search terms to use. Somehow, creating a draft hierarchy, for example.

Any hints into the right direction?

Untitled-1
Untitled-1747Ă—605 5.52 KB

2

Since you want the publication to be associated to only one draft I would do it this way:

  1. Create a “base” draft table with all the common attributes
  2. Have one table for each different type of draft. It will have a foreign key to the draft base table as well as the attributes specific to that type
  3. On your publication table have a foreign key to the draft table

This way seem simplest to me and let’s you have referential integrity relatively simply.

You will probably need to have some logic in your application to ensure an entry from the base table isn’t in more than one subtable. Or possibly check constraints on the database level.

2 Likes
3

BTW, this is called a “polymorphic association” because the publication is associated to more than one type of draft. The best reference I know of is the SQL antipatterns book. It has a small chapter on it with solutions (one of which is what I posted above)

2 Likes
4

Very helpful. Ty.