Complete guide to data schemas in ACC

Marcel Szimonisz
Apr 14, 2022
6 minutes read

Complete guide to data schemas in ACC

I want to gather as much information I have and have it at one place for my future reference and for others to learn what can be possible with the custom schemas in the Adobe Campaign Classic – ACC

Table of Contents

If you work with adobe campaign for some time you might figured out that almost any client’s sane or insane requirement can be put down and implemented in Adobe Campaign.

Schemas AKA tables

Every new feature starts with defining the database table structure that will store data coming from the forms. First we need to ask do we extend already a schema that is in place or do we create a new table.

Attributes / elements AKA columns

As the heading mentions attributes are basically columns in our newly created table. Search the actual documentation for “elements attributes”

If you want to see all enumeration values please take a look at the schema common. Where you can find all the system enumerations and attributes that can be reused in your custom schemas. You can find here element that adds audit trail (updated at, created at, created by, modified by fields) just simply add to your new schema definition.

<element aggregate="xtk:common:auditTrail" name="auditTrail"/>
<attribute enum="cus:enum:language" label="Language" length="2" name="language"
               type="string"/>
<attribute advanced="true" name="text1" template="nms:recipient:recipient/@text1"/>

aggregate – lets you copy the definition of an <element> via another schema. This attribute receives a schema declaration in the form of a “namespace:name”.¹

enum (string) – receives the name of the enumeration linked to the field. The enumeration can be inserted into the same schema or into a remote schema.¹

template (string) – this attribute defines a reference to an <element> element shared by several schemas. The definition is automatically copied into the current schema.¹

advanced – to be listed as advanced field in your schema views

If you have something that common schemas do not have and want to reuse it in multiple schemas of your own just create your own cus:common library and then simply refer its elements, attributes or enumeration in multiple places. This will give you centrally managed fields which we can all agree is a huge benefit.

Links AKA Table relations

As in any other relational database we can link tables

1:1 cardinality

One record from table X is related to a record in table Y.

  <element label="Service" name="service" revLink="service" target="nms:service"
             type="link"/>

Will resolve in the preview tab to

   <element label="Service" name="service" revLink="service" target="nms:service"
             type="link">
      <join xpath-dst="@id" xpath-src="@service-id"/>
    </element>

If you want apply any filter the linked table

   <element label="Newsletter template" name="newsletterTemplate" revLink="newsletterTemplate"
             target="nms:delivery" type="link">
      <join dstFilterExpr="@isModel = true" xpath-dst="@id" xpath-src="@newsletterTemplate-id"/>
    </element>

dstFilterExpr (string) – this attribute lets you restrict the number of eligible values in the remote table.²

External join

When we add externalJoin=”true” attribute to the link that means there is no record saved in the related table

   
	<element externalJoin="true" label="Country/Region" name="country" revLink="recipient"
             target="nms:country" type="link">
      <join xpath-dst="@isoA2" xpath-src="location/@countryCode"/>
    </element>

1:N cardinality

When a records from table have multiple related records in another table. Simple example is that recipient delivery (nms:delivery) has link to the delivery log table (nms:broadLogRcp) with 1:N cardinality that means one delivery can have multiple delivery log records linked.

Difference between 1:1 link is that 1:N link has to be added to the related table. So from the example delivery log to delivery link has to be added to the delivery log table.

<element name="delivery" revLabel="Recipient delivery logs"/>

Will resolve in preview tab to

    <element integrity="normal" label="Delivery" name="delivery" revLink="broadLogRcp"
             target="nms:delivery" type="link">
      <join xpath-dst="@id" xpath-src="@delivery-id"/>
    </element>

Here is very useful to mention integrity attribute. Which basically means what will happen to all our delivery when we remove linked delivery log record.

integrity (optional): referential integrity of the occurrence of the source table to the occurrence of the target table.

define: it is possible to delete the source occurrence if it is no longer referenced by a target occurrence,
normal: deleting the source occurrence initializes the keys of the link to the target occurrence (default mode), this type of integrity initializes all foreign keys,
own: deleting the source occurrence leads to the deletion of the target occurrence,
owncopy: the same as own (in case of deletion) or duplicates the occurrences (in case of duplication),
neutral: does nothing.³

Sometimes we want to enforce “cascade delete” e.g. when we remove delivery I want all delivery logs to be removed as well (just example)

In this case we will use revIntegrity attribute which is basically defining what will happen to the target table when the source table record is deleted

revIntegrity (optional): integrity on the target schema (optional, “normal” by default),³

To enable cascade delete we will choose the integrity type own

Next time we will show you how we add UI interface to our custom schemas.

TIP

All the possible options for every attribute can be found in schemas:

xtk:sqlSchema
xtk:srcSchema

Used references

Share With Others

3 Comments on “Complete guide to data schemas in ACC”

Partha
says:

August 9, 2022 at 7:38 am

Thanks for the Blog Marcel.
It will be helpful, If you guide one How to create N:N link in Adobe Campaign Classic with an example?

Thanks!

Marcel
says:

September 15, 2022 at 11:37 am

Hello Partha,
sorry for belated answer. thank you for reading my blog. It seems there is no ootb N:N cardinality. I would try to use 1:N link on both schemas linking them toghether.

Lakshmeesh Hegde
says:

August 22, 2023 at 12:59 pm

Many to many links have a relationship table helping them, where linked records are stored. Like most of the Rcp tables in ACC. Like Delivery and Recipient have many-to-many relationship, details are stored in broadLogRcp. Usually in database if two tables have many-to-many relationship, a relationship table is created.