Primary and Secondary Tables

In this chapter, we shall add another "secondary" table to the AdEntity which has a one-to-one relation to the primary "ad_creatives" table. An entity that contains "children" using one-to-many relations shall be discussed on a different chapter. This is just the simple case of 1-to-1.

Our secondary table name is "ad_params". We first define it in Java using JOOQ:

public class AdParams extends AbstractDataTable<AdParams> {

  public static final AdParams TABLE = new AdParams ("ad_params");
  private AdParams(String name) { super(name); }

  public final TableField<Record, Integer> creative_id = createFKField("creative_id", AdCreative.TABLE.creative_id);

  public final TableField<Record, String>  param1      = createField("param1", SQLDataType.VARCHAR.length(50));

  public final TableField<Record, String>  param2      = createField("param2", SQLDataType.VARCHAR.length(50));

  public final TableField<Record, String>  param3      = createField("param3", SQLDataType.VARCHAR.length(50));
}

So it has 3 param fields (field1, field2, field3) and a foreign key (FK) to the creative_id of the AdCreative table.
Note how the FK field is created using the createFKField method.
PL does not require that a real FK is defined in the SQL server schema. But it does require this secondary table to have a unique index in the schema so that it could perform INSERT ... ON DUPLICATE UPDATE.

The secondary table shall have a FK to the primary table.  
A primary table referring to the secondary table is not a valid scenario.

And believe it or not, that's it. We can add fields of the secondary table directly to the entity.

public class AdEntity extends AbstractEntityType<AdEntity> {

    // ... definitions from previous chapters ... //

    //
    // fields from the primary table
    //    
    @Immutable
    public static final EntityField<AdEntity, Integer>    ID = INSTANCE.field(AdCreative.TABLE.creative_id);

    public static final EntityField<AdEntity, SyncStatus> SYNC_STATUS = INSTANCE.field(AdCreative.TABLE.status);

    public static final EntityField<AdEntity, String>     HEADLINE = INSTANCE.field(AdCreative.TABLE.headline);

    public static final EntityField<AdEntity, String>     URL = INSTANCE.field(AdCreative.TABLE.display_url);

    //
    // fields from the secondary table
    //    
    public static final EntityField<AdEntity, String>     PARAM1 = INSTANCE.field(AdParams.TABLE.param1);

    public static final EntityField<AdEntity, String>     PARAM2 = INSTANCE.field(AdParams.TABLE.param1);

    public static final EntityField<AdEntity, String>     PARAM3 = INSTANCE.field(AdParams.TABLE.param1);

}

You don't need to care anymore which field belongs to which table. PL will do everything for you.
For instance, when you insert a new Ad entity with some Ad Param, the creative_id shall be automatically populated to the ad_params table.
Inserting a new Ad without any AdParam field does not create an empty row in the ad_params table. An entry in the secondary table shall be created the first time a secondary field exists in the Ad command.