A many-to-many relationship occurs when one or more rows in a table are associated with one or more rows in another table. This is a conceptual design pattern and can be achieved in the database by adding a join table representing the many to many relationship between the two main tables as two individual one to many relationships between each main table and the join table
Two one-to-many relationships established between the two main tables are not the same as one many-to-many relationship. A many-to-many relationship cannot be created using the "@OneToMany" annotation, but keep in mind that Appian supports an annotation to create a many-to-many relationship: "@ManyToMany".
To further clarify: two unidirectional one-to-many relationships will create two mapping tables for the relationship, whereas a unidirectional many-to-many relationship will only create one mapping table. Also, with two unidirectional one-to-many relationships, both entities know about each other, whereas on a truly unidirectional many-to-many relationship, only one entity knows about the other.
Unidirectional = Only one of the entities knows about the other.
Bidirectional = Both entities know about each other.
Owner = Entity that stores the id of the other.
Inverse = The entity referred to by the owner.
* One-to-One bidirectional tables are both technically owners, but JPA requires that one is defined as an inverse.
Click here for further reading on these relationships and how they work: http://meri-stuff.blogspot.com/2012/03/jpa-tutorial.html
The join table in this example maps one student id to one class id.
We can create a CDT in Appian using an XSD and JPA annotation which models the many-to-many relationship using the JPA annotation @ManyToMany.
@SecondaryTable joins one or more tables together, appears flat but actually has a parent/child “hidden” structure, appears like a one-to-one, and is used rarely.
The @SecondaryTable annotation is used to specify the secondary tables for the annotated entity class. It only applies to a very specific and limited use case: a single entity that is spread across multiple tables. The tables share the same key and are in a one-to-one relationship, and the annotation is meant exclusively to join two tables where the secondary table has a column that matches the primary table's primary key column.
For example, in this diagram, the primary key in class - ID - matches to the column CLASS_ID in CLASS_DETAIL:
The purpose is to be able to split tables with a large number of columns into several tables, and then be able to create a CDT that seamlessly joins all the tables to mimic one big table - much like a view. Specifying one or more secondary tables indicates that the data for the entity is stored across multiple tables.
Please note that @SecondaryTable annotation is not meant to join lookup or reference tables that map against specific non-primary key columns. For mapping lookup or reference data, a regular nested CDT is required - see Nested CDT Structures section for further information.
For further reading on this topic, click here: http://en.wikibooks.org/wiki/Java_Persistence/Tables#Multiple_tables_with_foreign_keys
http://en.wikibooks.org/wiki/Java_Persistence/Tables#Multiple_tables
XSD example: CLASS_WITH_DETAIL.zip
When working with a large number of columns in a table, you may run into the issue of size limits. For example, MySql imposes a 65535-byte limit on a single table. To avoid this issue, consider using the @SecondaryTable annotation in your XSD file, to allow you to split the data into more than one table, while still being able to use the same CDT structure. With this annotation, you can split your data across smaller tables and have them seamlessly join off of a primary key, and the final result will behave as one table.
Within the <xsd:annotation> structure, add the following annotation:
@SecondaryTable(name="NameOfSecondaryTable", pkJoinColumns=@PrimaryKeyJoinColumn(name=<COLUMN_NAME_OF_PRIMARY_KEY_IN_SECONDARY_TABLE>,referencedColumNName="COLUMN_NAME_OF_FOREIGN_KEY_IN_PRIMARY_TABLE")
In the CDT, define the fields of the secondary table using the following annotation:
@Column(table="NameOfSecondaryTable>", name="COLUMN_NAME_IN_SECONDARY_TABLE")
When querying, writing to, or deleting from this datastore entity, you can proceed as if the CDT was written without this annotation. Appian will write the data to the appropriate primary and secondary tables, linking the two by the specified primary and foreign keys.
Nested CDTs can be used for the retrieval and storage of individual attributes or nested structures, reducing query calls in some cases when nested. For example, bringing back associated lookup data when returning a CDT - which can be a one-to-one or one-to-many relationship.
For example - if we add a column to STUDENT to denote the major the student has chosen - the CDT can be nested to bring back the relevant MAJOR_REF at the same time as the STUDENT.
XSD Example: MAJOR.zip STUDENT_WITH_MAJOR.zip
None of these settings are inherently good or bad. It depends on what you're trying to do. Persist and Remove can be very useful to simplify writes to the DB to apply changes by not having to create separate logic to handle deletes. Refresh has the least associated risk and should always be used for reference data. Before making a decision, be aware of the effects each of these settings has on your data.
Using a list of primitives - instead of a real lookup table with an id - is a bad idea for multiple reasons:
When using flat CDTs, Appian will not create any foreign key relationships when publishing datastores and creating tables automatically. The lack of foreign keys causes lookups to not perform well due to lack of indices in the data model.
There are two ways to manage this