CAP double foreign key issuse

[former_member824324]

Hello experts,

I'm having a problem with a rather particular schema: I have a table (DocumentItem) which represents all the items that are in "DocumentHeader". The primary keys are:

- DocumentHeader -> DocumentId

- DocumentItem -> DocumentId * (foreign key) and ItemNumber

The problem comes when in another table I need DocumentId (taken from DocumentHeader) and ItemNumber (taken from DocumentItem) as foreign keys. I also need that the ItemNumber is correctly associated with the DocumentID, as declared in the "DocumentItem" table

The problem in question is that it doesn't show me when I go to insert "ItemNumber" mock data, going to show it only if I remove the "key" property.

Thanks to all, I am available for clarification

general schema:

schema.cds

entity DocumentHeader
{
    key DocumentId : String
        @Core.Computed;
    Status : String(100) not null;
    CustomerId : String(100) not null;
    TotalValue : Double not null;
    Currency : String(100) not null;
    TotalQuantity : Double not null;
    UoM : String(100) not null;
    to_DeliveryAddress : Association to one Customer
        @assert.integrity;
    DocumentDate : String(100) not null;
    CustomerOrder : String(100) not null;
    RequestDeliveryDate : String(100) not null;
    EffectiveDeliveryDate : String(100) not null;
    DeliveryStatus : String(100) not null;
    DeliveryBloc : String(100) not null;
    IncoiceBloc : String(100) not null;
    PayConditions : String(100) not null;
    ShipToPartnerId : String(100) not null;
    to_DocumentItems : Association to many DocumentItem
        @assert.integrity;
    to_Partners : Association to many Partner on to_Partners.DocumentId = $self
        @assert.integrity;
    to_PriceConditions : Association to many PriceCondition on to_PriceConditions.DocumentId = $self
        @assert.integrity;
    to_DeliveryPlans : Association to many DeliveryPlan on to_DeliveryPlans.DocumentId = $self
        @assert.integrity;
    to_Notes : Association to many Note on to_Notes.DocumentId = $self;
}

entity Note
{
    key NoteType : String
        @Core.Computed;
    NoteDesc : String(100) not null;
    NoteText : String(100) not null;
    key ItemNumber : Association to one DocumentItem
        @assert.integrity;
    DocumentId : Association to one DocumentHeader;
}

entity DocumentItem
{
    key ItemNumber : String
        @Core.Computed;
    ProductId : String(100) not null;
    Quantity : Double not null;
    UoM : String(100) not null;
    Price : Double not null;
    Currency : String(100) not null;
    Discounts : Double not null;
    TotalValue : Double not null;
    ItemStatus : String(100) not null;
    Incoterms : String(100) not null;
    EffectiveGoodIssue : String(100) not null;
    DeliveryStatus : String(100) not null;
    ItemCategory : String(100) not null;
    BatchStatus : String(100) not null;
    ATPDate : String(100) not null;
    key DocumentId : Association to one DocumentHeader
        @assert.integrity;
    to_Partners : Association to many Partner on to_Partners.ItemNumber = $self
        @assert.integrity;
    to_PriceConditions : Association to many PriceCondition on to_PriceConditions.ItemNumber = $self
        @assert.integrity;
    to_DeliveryPlans : Association to many DeliveryPlan on to_DeliveryPlans.ItemNumber = $self
        @assert.integrity;
    to_Notes : Association to many Note on to_Notes.ItemNumber = $self;
}

Mock Data ("ItemNumber" is missing):

[falcaraz]

You want notes to depend on items, and items to depend on headers? I'm confused because there's an association in headers to notes, but notes includes a key association to items.

[former_member824324]

francisco.javier.alcaraz.fernandez for a number of reasons, the structure is as follows: the documentHeader has an id, the document header has several documentItems (which have the combination of ItemNumber and DocumentHeaderID as their identifier) and then the other tables (Note is one of those) have keys external DocumentHeaderID (taken from the documentHeader) and the ItemNumber (of the DocumentItem).

[falcaraz · Accepted solution]

Assuming you want both headers and items to have notes, I'd do something like this and let aspects handle things (unmanaged associations).

namespace someNamespace;

entity Documents {
    key ID : String @Core.Computed;
    status : String(100) not null;
    customerId : String(100) not null;
    totalValue : Double not null;
    currency : String(100) not null;
    totalQuantity : Double not null;
    uoM : String(100) not null;
    documentDate : String(100) not null;
    customerOrder : String(100) not null;
    requestDeliveryDate : String(100) not null;
    effectiveDeliveryDate : String(100) not null;
    deliveryStatus : String(100) not null;
    deliveryBloc : String(100) not null;
    incoiceBloc : String(100) not null;
    payConditions : String(100) not null;
    shipToPartnerId : String(100) not null;
    items : Composition of DocumentItems;
    notes : Composition of DocumentNotes;
}

aspect DocumentItems {
    key ID : String @Core.Computed;
    productId : String(100) not null;
    quantity : Double not null;
    ioM : String(100) not null;
    price : Double not null;
    currency : String(100) not null;
    discounts : Double not null;
    totalValue : Double not null;
    itemStatus : String(100) not null;
    incoterms : String(100) not null;
    effectiveGoodIssue : String(100) not null;
    deliveryStatus : String(100) not null;
    itemCategory : String(100) not null;
    batchStatus : String(100) not null;
    aTPDate : String(100) not null;
    notes : Composition of ItemNotes;
}

aspect Notes {
    key ID : String @Core.Computed;
    description : String(100) not null;
    text : String(100) not null;
}

aspect DocumentNotes : Notes { 

};

aspect ItemNotes : Notes { 
    
};

This creates the constrains automatically too, if you check the hdbtable preview you can see it

[falcaraz · Accepted solution]

I'd also change the type of the keys because if this is what you need, you'd have a composite key of NVARCHAR(15000) in notes for items.