How to resolve Database naming convention issues ?

Hi  I have two fields as checkbox on my form (ToolsAffected and NodesAffected). To save those values i have used them to be as array values insted of going into nested CDT while designing my CDT. Here is the screenshot of my CDT that i designed for that purpose - 

I didn't create any tables in my MS SQL Server before publishing the data type to data store. I used the DDL script generated by Appian itself. Here is the query - 

create table [cccdetails_nodesaffected] (
        [cccdtils_ndsffcted_cccnmber] int not null,
        elt nvarchar(255) null,
        [cccdetails_ndesaffected_idx] int not null,
        primary key ([cccdtils_ndsffcted_cccnmber], [cccdetails_ndesaffected_idx])
    );

    create table [cccdetails_toolsaffected] (
        [cccdtils_tlsffcted_cccnmber] int not null,
        elt nvarchar(255) null,
        [cccdetails_tolsaffected_idx] int not null,
        primary key ([cccdtils_tlsffcted_cccnmber], [cccdetails_tolsaffected_idx])
    );

    create table [cccdetails] (
        [cccnumber] int identity not null,
        [approved] nvarchar(255) null,
        [approvaldate] datetime null,
        [prnumber] nvarchar(255) null,
        [ecr] nvarchar(255) null,
        [description] nvarchar(200) null,
        [class] nvarchar(5) null,
        [reclass] nvarchar(5) null,
        [sixmonthsmaterialrequired] nvarchar(5) null,
        [changetype] nvarchar(255) null,
        [notificationdate] datetime null,
        [cccpresenter] nvarchar(255) null,
        [ineccos] nvarchar(5) null,
        [dateaddedtoeccos] datetime null,
        [ceoldbompn] nvarchar(255) null,
        [oldrevision] nvarchar(255) null,
        [cenewbompn] nvarchar(255) null,
        [newrevision] nvarchar(255) null,
        [productgroupaffected] nvarchar(255) null,
        [approvedby] nvarchar(100) null,
        [spared] nvarchar(5) null,
        [sbom] nvarchar(5) null,
        [betarequired] nvarchar(5) null,
        [betarequested] nvarchar(5) null,
        [bundled] nvarchar(5) null,
        [bundleqtr] int null,
        [bundlepresentdate] datetime null,
        [qualstatus] nvarchar(255) null,
        [qualpartsdate] datetime null,
        [qualapprovaldate] datetime null,
        [comments] nvarchar(255) null,
        [createdby] nvarchar(100) null,
        [createdon] datetime null,
        [updatedby] nvarchar(100) null,
        [updatedon] datetime null,
        primary key ([cccnumber])
    );

    alter table [cccdetails_nodesaffected] 
        add constraint FKAFE31CC5F1D89349 
        foreign key ([cccdtils_ndsffcted_cccnmber]) 
        references [cccdetails];

    alter table [cccdetails_toolsaffected] 
        add constraint FK5ACB09EF1292D00B 
        foreign key ([cccdtils_tlsffcted_cccnmber]) 
        references [cccdetails];

When i checked in the CDT, It is - 

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:CCCD" targetNamespace="urn:com:appian:types:CCCD">
  <xsd:complexType name="CCCDetails">
    <xsd:annotation>
      <xsd:documentation><![CDATA[Information about CCC in the CCC Application]]></xsd:documentation>
    </xsd:annotation>
    <xsd:sequence>
      <xsd:element name="CCCNumber" nillable="true" type="xsd:int">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Id @GeneratedValue</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Approved" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="ApprovalDate" nillable="true" type="xsd:date" />
      <xsd:element name="PRNumber" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="ECR" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Description" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=200)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Class" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Reclass" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="SixMonthsMaterialRequired" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="ChangeType" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="NotificationDate" nillable="true" type="xsd:date" />
      <xsd:element name="CCCPresenter" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="InEccos" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="DateAddedToEccos" nillable="true" type="xsd:date" />
      <xsd:element name="CEOldBomPN" nillable="true" type="xsd:string" />
      <xsd:element name="OldRevision" nillable="true" type="xsd:string" />
      <xsd:element name="CENewBomPN" nillable="true" type="xsd:string" />
      <xsd:element name="NewRevision" nillable="true" type="xsd:string" />
      <xsd:element name="ProductGroupAffected" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="ToolsAffected" nillable="true" type="xsd:string" />
      <xsd:element maxOccurs="unbounded" minOccurs="0" name="NodesAffected" nillable="true" type="xsd:string" />
      <xsd:element name="ApprovedBy" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Spared" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Sbom" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BetaRequired" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BetaRequested" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="Bundled" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="BundleQtr" nillable="true" type="xsd:int" />
      <xsd:element name="BundlePresentDate" nillable="true" type="xsd:date" />
      <xsd:element name="QualStatus" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="QualPartsDate" nillable="true" type="xsd:date" />
      <xsd:element name="QualApprovalDate" nillable="true" type="xsd:date" />
      <xsd:element name="Comments" nillable="true" type="xsd:string" />
      <xsd:element name="CreatedBy" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="CreatedOn" nillable="true" type="xsd:dateTime" />
      <xsd:element name="UpdatedBy" nillable="true" type="xsd:string">
        <xsd:annotation>
          <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo>
        </xsd:annotation>
      </xsd:element>
      <xsd:element name="UpdatedOn" nillable="true" type="xsd:dateTime" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

I want to rename the CCCDetails_ToolsAffected and CCCDetals_NodesAffected table name as well as column names without changing PK/FKs. But i couldn't find these names in XSD. Can you please help me in fixing it ?

My personal preference is not to use Appian to generate the tables. I'd suggest you read the documentation as there's a lot of good information regarding database schema best practices amongst other things.

If you're happy with badly-named items in the database - such as constraints named 'FK5ACB09EF1292D00B' - and still wish to continue using Appian to generate the tables, you'll probably be better off dropping the tables that were created, changing the XSDs and then republishing the XSD and using the DDL to recreate the tables.

Hi , I am not happy with the badly named items, I want to remove all the underscores and other non-informative words. That's why i am looking for help to rename these fields. But there is nothing that i can change in my CDT in designer mode. I even tried to use the same query and renamed all the table name and column name at DB level itself and then i tried to create CDT using my existing table from DB but i was not able to see the two main fields (ToolsAffected and NodesAffected) as array value. How can i use 3 tables, 1 parent and 2 child table to create a CDT which will have two fields as array (ToolsAffected and NodesAffected) . Thank you for your help, I will look into the link you suggested.

You're not going to be able to rename without starting from scratch.

What you actually want to do is create three CDTs and nest two of them in the parent with a Many-to-One relationship. Documentation for this is available here: docs.appian.com/.../CDT_Relationships.html

If you're still struggling, I'd recommend watching the data design videos on Appian Academy.

Hi ,

I think the main problem is when we have primitive array referred in a CDT. We can always create tables by our self with proper naming conventions but in case there is primitive array referred in a CDT and if that is used to in data-store I faced problems publishing the DS though I have created the tables in DB it always asked me to create the supplementary tables which facilitate the array values for the filed when i tried to publish the DS. This does not happen with any other scenario but only when we have a primitive array referred. I tried but had no luck. If someone have done this please do let know

Regards
Suresh