This article will have an overview of the most recommended approaches while you are designing database schema. Without further ado, let us jump into the topic and understand its technical aspects.
To start with, you may keep your assigned data types names and all CDT elements being lesser than 27 characters. Names of the database tables, columns in it, and the SQL objects from the XSD may get truncated if the character length exceeds 27 characters. This is so to maintain compatibility with all the supported databases. You may consider using the JPA annotations in XSD to cut out the risk of truncation of any change in the letter casing. The name attributes for @Column and @Table are used while referencing different data types for data stores.
Generating database tables from CDT
Truncation of longer than 27 character SQL names to CDT element and data types may occur in the below manner while delivering the names of database tables and columns.
- Vowels get removed from it in the order of u, o, a, e, i. The names which begin with a vowel, however, retain only that vowel.
- The name of the CDT element or data type may get split with underscores.
- The longest segment of the name may get trimmed by one character each time until it is shortened to become 27 characters.
- If there are two similar longer names for the columns, then those two may get truncated to the same value.
Use the @Column and @Table annotations for defining table names within the 27-character limits, which should be used if the element types exceed the target.
Column Types
The column type is inferred from the XSD type as per the associated data type. While mapping it to the existing data tables, if the inferred column type does not match the type of the existing column, you may have to explicitly define column type using @Column annotation with a column definition attribute.
Then the database will return values with blanks if the length of the given value is less than the specified N. While used in association with the data type annotated with @Version annotation, then this may cause further issues by triggering incremental version values. This is primarily because the spaces trailing may be trimmed from the to be stored value.
If it is not desirable, you much consider VARCHAR usage or something equivalent to target DB. Considering the databases that support the same, only MySQL will help trim the padding from the returned value and not trigger any version update. For the integer values, you may use XSD: int as element type, which has a similar maximum value as the CDT field of the type Number or the Integer. For more info on the default column types based on XSD types, see Generating Database Tables from the CDTs page. If you are confused about implementing an apt database schema, try to get expert support from providers like RemoteDBA.
Composite keys
While working with tables having composite keys, we may recommend that you may either find a column that is already existing in the table with unique values or try to create another column with some unique values and then map it to the field of CDT with an @Id annotation.
Tables having composite keys are supported in various ways as:
- The ‘Data Store Entities’ are mapped to tables having composite keys as the @EmbeddedId annotation may not be used while mapping the CDT fields to the key columns.
- The function of a!queryEntity() can read the data from the tables with composite keys. Still, the totalCount and identifiers fields of data may return the subsets, which will sometimes be incorrect unless there is a unique separate column there in the table to be mapped to a particular field in the CDT marked with @Id annotation.
- The smart services like ‘Write to Data Store Entity’ or the query rules may work only with the tables with composite keys with a unique column in the table mapped to a field in CDT, which is annotated with the @Id annotation.
DDL Editing
There are many differences between existing schemas and CTC, which can be detected easily, but we cannot easily resolve them. In those cases, you have to first alter the tables in the database by downloading those and executing the DDL scripts. If there are any mismatch in XSD element types and the column types corresponding to it, the system may not alter any column type. If the given XSD has some specific type or @Table annotation, then it is mapped to its synonyms, and then the systems also cannot validate if those types of tables exist.
It will also be necessary to have to dop all the existing tables and create them again by using the DDL generated. This will include the code, which can be further used for the creation of the same.
Null values
Empty text process variables may be handled as empty strings by the system, but not as “null” values. This will also let the blank strings to insert into the same data store. If there are any corresponding columns in the RDBMS, which are “not null,” then the database may not prevent any empty strings from getting inserted.
You may also consider using the annotation of @Version on the CDT fields to enable the optimistic locking on given data objects. Without using the annotation of @Version, if there is a secondary process that has updated any object in the data stores since the current process read it last, then the value stored by another secondary process gets overwritten. With the annotation of @Version, the database may detect that the values which are stored by the given process remains stale and may also prevent any update. The given @Version annotation may be used on only a single field per each CDT.
You may try all these practices while designing database schema, which helps you ensure optimum performance and output from the given database.
