What does an entity one-to-one relationship look like in the database?

Question

I am trying to create a SQL query between policyperiod and effectDatedFields to get the effectedDatedFields record for a specific policyperiod. I am not sure how to do this. I cannot find any foreign key in either table pointing to the other. Any help would be greatly appreciated.

I have looked at every column in both tables for something to join on. I have looked at the data dictionary for both entities for the same. I have even went on guidewire education and look at some of the data model sections to try and figure out how to model a one-to-one relationship in the database.

Answer 1

The pc_effectivedatedfields table contains a field called BranchID. This field can be used to join this table with pc_policyperiod table using ID column. However, it's important to note that the PolicyPeriod entity is of type effdatedbranch, while the pc_effectivedatedfields table belongs to the effdated type. Consequently, if there have been policy changes to the policy during its term and any of the fields in the pc_effectivedatedfields table have been modified, you will encounter multiple entries in the pc_effectivedatedfields table for a single pc_policyperiod entry.

If you'd like to gain a better understanding of how slicing works in PolicyCenter, I would suggest taking some time to experiment in PolicyCenter and observe the results directly within the database. It can be a valuable hands-on experience to enhance your comprehension of this concept.

Answer 2

The one-to-one data model element is meant to allow for a circular reference (for all practical purposes), but without actually creating a circular reference in the DB. For this reason, the actual database foreign key associating the tables with each other is just in 1 table. From your question it looks like you are investigated PolicyCenter. My answer contains an example from ClaimCenter because that is all I have at my disposal right now, but the principle should be the same.

In ClaimCenter the Claim entity is the top-level entity and it has a onetoone reference to SubrogationSummary. The SubrogationSummary entity has a foriegnkey reference to the Claim entity. In the database a ClaimID column exists on the cc_subrogationsummary table, which associates one row of the cc_subrogationsummary table with one row of the cc_claim table. There is no column in the cc_claim table which directly references the cc_subrogationsummary table. In Gosu code, when a Claim entity is loaded from the data model layer, the Claim.SubrogationSummary is allowed because the data model (because of the onetoone defined in the Claim entity) knows that one and only one SubrogationSummary can be associated with this claim.

Answer 3

Also to add to what @Gatis mentioned in some special circumstances the "multiple" slices can occur also on Submission. In the OOTB documentation there is a very comprehensive chapter on "Policy revisioning" - I strongly suggest to read it.

For your challenge you have to incorporate the BranchId and if you will have multiple slices you can further filter them by effectivedate and expirationdate. However be aware that there might be slices starting on the same date.

Answer 4

A small clarification for @Gonzo related to @Gwisard s response. At the end Gwisard states "there might by multiple slices starting on the same date". While that's true from a policy transaction basis (that is, you can have multiple jobs that used the same EditEffectiveDate), once you're at the level of an effdated entity there cannot be overlapping date ranges within a single PolicyPeriod (aka branch). A set of entity rows (that is, rows with the same fixedID and BranchValue) can have gaps between one expiration date and the next effective date. But there will never be cases where the date values overlap. Trying to create such data will result in an exception at the DB commit.

In gosu code the best way to see these records is via a VersionList.

Answer 5

The other answers about effective dated fields and slicing are good, but I wanted to provide clarification on a statement from @SteveDrippsCentricConsulting who said, "The one-to-one data model element is meant to allow for a circular reference (for all practical purposes), but without actually creating a circular reference in the DB." This part about the Guidewire entity model isn't quite right.

If you want to create a "soft" or indirect circular reference, without creating a hard circular reference in the database (which is bad), you should use an Edge Foreign Key which extracts the relationship into an association table.

The purpose of a one-to-one relationship is to indicate a foreign key relationship between two tables with a cardinality of one (hence one-to-one). It is analogous to an array relationship which indicates a foreign key relationship with a cardinality of one-to-many. Essentially, a one-to-one is an array relationship limited to a single element.

From a data perspective, both basically look the same. FK relationships are typically defined as a parent-child relationship with the FK column on the child (this is for most RDBMSs). In Guidewire, a properly defined one-to-one or array relationship will be defined on the parent table, with a corresponding FK column on the child table, and the platform will automatically define a FK constraint between the two. Failure to define the FK on the child will cause a warning to be displayed in ISStudio. (Note that the opposite is not true: if you define a FK on the child and do NOT define a one-to-one or array on the parent, you will not get a warning; the platform assumes you are attempting to create a one-way FK without a DB constraint. Many a developer has created a one-way FK relationship when they should have created a one-to-one relationship which is two-way and automatically generates a DB constraint.)

The difference between a one-to-one and array is that the Guidewire platform automatically enforces the cardinality of one in the DB and in the application. Additionally, the Guidewire platform provides auto-deletion of child records. When you define a one-to-one or array, make sure the attribute "cascadeDelete" is set to true, so when a parent record is deleted, the child record is also deleted. Failure to do this can cause orphan records, a common cause of DBCC errors, and issues with Purge and/or Archiving.

Also note that you can change the standard parent-child behavior of a FK relationship. This can be done on the child table in the FK definition using the attribute "archivingOwner." The default value is "target" meaning the target of the FK is the parent (standard FK relationship). Setting the value to "source" indicates that the entity with the FK is acting as the parent, indicating a reverse FK relationship. The value "none" indicates no parent-child relationship at all. Altering the relationship using the values "source" or "none" can cause issues with orphan records, Purge, Archiving, and if not properly designed could cause data loss or corruption. Any such change must be carefully considered and tested.