Referential Integrity

[ashwken]

The parent child relationship allows us to normalize the data structure:

for example, an invoice has a header section with a unique identifier, a date stamp, lookups to Contacts for Sold To, Ship To, Bill To (all the information to describe the invoice)

then you have one or more child items decribing what is being sold.

One method to insure referential integrity between parent and child records, is to create an attribute in the child record whose value is the unique identifier of the parent. In UR this is handled by the Data Explorer tree, but what happens if child records get misplaced by user error, become detached from their rightful inheritance.

As far as I can tell, this is partailly handled by the use of the Attribute Type - Info Item in the child record, but requires user input to set its value to anything beyond a default location.

Did a search on the title and came up empty, so I'm not sure if this has been addressed in v.4, but I am curious how others deal with this.

[wordmuse]

so far as I know - there is no way to create an attribute that requires unique data to populate a record - nor is there any way to have an attribute automatically increment (which would be one way of creating a unique id for each item).

I'd love to know that I'm mistaken - it would be very helpful, in fact.

- Bal

[ashwken]

The Invoice example may have been a poor choice, but I'm going to stick with it. I realize that UR does not offer an Attribute Option to sequential autonumber, it's just part of the example.

Here's a typical data structure for an Invoice parent item:
Unique Item ID (node ID)
Invoice Number (sequential autonumber)
Date
Sold To
Ship To
Bill To
Terms

Here's a typical data structure for an Invoice child item:
Unique Item ID (node ID)
Unique Item ID of parent (Info Item, lookup)
Part Number
Quantity
Unit Price

To establish a permanent linkage (Referential Integrity) between the parent and child - independent of the Data Explorer - you must use the Attribute Type - Info Item for the Attribute (Unique Item ID of parent) in the child item, which serves as the permanent link to the parent (forgein key).

When the child record is created, this type of forgein key assignment must still be completed by the user. Perhaps this is more of a Suggestion than a Discussion, just not sure if I'm missing something.

[J-Mac]

Ken,

I always thought that child items were connected to parents some how other than the Data Explorer position. However I haven't looked into the exact "how" it is done. I guess that the fact that a child item can have more than one parent item helped to lead me into thinking that way. If a child item has multiple parents - but can only be positioned under one parent item in the Data Explorer - how does UR keep track of the other parent items internally?

Thanks!

Jim

[ashwken]

Originally posted by J-Mac

I always thought that child items were connected to parents some how other than the Data Explorer position. However I haven't looked into the exact "how" it is done. I guess that the fact that a child item can have more than one parent item helped to lead me into thinking that way. If a child item has multiple parents - but can only be positioned under one parent item in the Data Explorer - how does UR keep track of the other parent items internally?

Yes, the Data Explorer allows you to create the parent-child relationship, and the relationship is maintained as long as the children do not wander off (due to user error).

The example of the Invoice is a case where you really don't want the children to wander about, here's another example to consider:

Software Title (Parent)
Unique Item ID (node ID)
SW_Title
SW_Tilte_Edition (Pick List)
SW_Vendor (Info Item, lookup)
SW_Summary

Software Title Version (Child)
Unique Item ID (node ID)
Unique Item ID of parent (Info Item, lookup)
SW_Ver_Date
SW_Ver
SW_Ver_dType (Pick List for dateType)
other Attributes about SW_Ver

The inclusion of an Info Item type Attribute (user Lookup to Parent) in the child Item allows you to "pin a name tag on each child". I guess what I'm looking for is an "automatic pining" option (forgien key assignment).