DynamoDB PetStore ER Model

The OpenApi PetStore.yaml schema specification defines "only" the form of the data to be exchanged among the service and client. The actual data may be stored in a different form in the service database and queries performed by the service on that database return the data in the form specified in the schema specification. To better understand the nature of the data exchanged in the PetStore application, we can derive a more general Entity Relation (ER) model of that data. This model will be used to discuss how data is eventually stored in the DynamoDB NoSQL database.

Here is a simple "crow's foot" ER model derived from the PetStore OpenApi specification components section.

Note: A cursory examination of this PetStore application ER model uncovers its incomplete coverage for even the most rudimentary order process one might actually implement. For instance, what happens if an order is canceled? What happens if the pet becomes unavailable for some other reason that being sold? Is it really OK for a Pet to have no category assigned to it? Should the Tag entity allow multiple Tags having the same Name? Should the Category entity allow for multiple Categories having the same Name?

However flawed this ER model may be, it is a "sufficient" model for our discussion as it demonstrates many important data relations used in most real applications:

• Many to Many: Pet → PetTag ← Tag
• One or More Pet → PhotoUrl
• Zero or More: Pet → Category
• Enumeration:
  • Pet → PetStatus
  • Order → OrderStatus

Note: Normally, we would start with an ER model and generate the OpenApi components specification from that. Here we have an existing OpenApi specification and will explain how that specification implements the ER model we derived from that OpenApi specification.

Many to Many

A Tag entity is related to zero or more PetTag entities and the Pet entity is related to zero or more PetTag entities. This creates a Many to Many relation among the Pet and Tag entities.

Here is the components section of the PetStore.yaml file defining the Pet and Tag entities.

openapi: 3.0.0
...
components:
    schemas:
        Pet:
            type: object
            required:
            - name
            - photoUrls
            properties:
                id:
                    type: integer
                    format: int64
                category:
                    $ref: '#/components/schemas/Category'
                name:
                    type: string
                    example: doggie
                photoUrls:
                    type: array
                    items:
                        type: string
                tags:
                    type: array
                    items:
                        $ref: '#/components/schemas/Tag'
                petStatus:
                    type: string
                    description: pet status in the store
                    $ref: '#/components/schemas/PetStatus'
                createUtcTick:
                    type: integer
                    format: int64
                updateUtcTick:
                    type: integer
                    format: int64
        Tag:
            type: object
            properties:
                id:
                    type: integer
                    format: int64
                name:
                    type: string

The Pet.tags property is an array of zero or more Tag entities where Tag entities are associated with zero or more Pet entities. In the ER model this is modeled with a join entity called PetTag.

The generated schema classes supporting this OpenApi specification of Pet and Tag look like this:

namespace PetStoreSchema.Models
{
    using System = global::System;
     
    [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.3.3.0 (Newtonsoft.Json v12.0.0.2)")]
    public partial class Pet 
    {
        [Newtonsoft.Json.JsonProperty("id", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public long Id { get; set; }
    
        [Newtonsoft.Json.JsonProperty("category", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public Category Category { get; set; }
    
        [Newtonsoft.Json.JsonProperty("name", Required = Newtonsoft.Json.Required.Always)]
        public string Name { get; set; }
    
        [Newtonsoft.Json.JsonProperty("photoUrls", Required = Newtonsoft.Json.Required.Always)]
        public System.Collections.Generic.ICollection<string> PhotoUrls { get; set; } = new System.Collections.ObjectModel.Collection<string>();
    
        [Newtonsoft.Json.JsonProperty("tags", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public System.Collections.Generic.ICollection<Tag> Tags { get; set; }
    
        /// 
pet status in the store
        [Newtonsoft.Json.JsonProperty("petStatus", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        [Newtonsoft.Json.JsonConverter(typeof(Newtonsoft.Json.Converters.StringEnumConverter))]
        public PetStatus PetStatus { get; set; }
    
        [Newtonsoft.Json.JsonProperty("createUtcTick", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public long CreateUtcTick { get; set; }
    
        [Newtonsoft.Json.JsonProperty("updateUtcTick", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public long UpdateUtcTick { get; set; }
    
        private System.Collections.Generic.IDictionary<string, object> _additionalProperties = new System.Collections.Generic.Dictionary<string, object>();
    
        [Newtonsoft.Json.JsonExtensionData]
        public System.Collections.Generic.IDictionary<string, object> AdditionalProperties
        {
            get { return _additionalProperties; }
            set { _additionalProperties = value; }
        }
    }
}

namespace PetStoreSchema.Models
{
    using System = global::System;
    
    [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.3.3.0 (Newtonsoft.Json v12.0.0.2)")]
    public partial class Tag 
    {
        [Newtonsoft.Json.JsonProperty("id", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public long Id { get; set; }
    
        [Newtonsoft.Json.JsonProperty("name", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public string Name { get; set; }
    
        private System.Collections.Generic.IDictionary<string, object> _additionalProperties = new System.Collections.Generic.Dictionary<string, object>();
    
        [Newtonsoft.Json.JsonExtensionData]
        public System.Collections.Generic.IDictionary<string, object> AdditionalProperties
        {
            get { return _additionalProperties; }
            set { _additionalProperties = value; }
        }
    }
}

This schema is a "denormalized" view of the many to many Pet and Tag entity relation specified in the ER model. Remember that this OpenApi specification, and the resultant C# classes, only specifies the form of the data transferred among the host and client. These entities might be stored in a different form in a database and the query process may generate this denormalized form.

Pet & Tag Alternative Representations

There is no single "best" representation for the data to be transferred among the host and client - there is an optimal representation for each specific use-case. For instance, instead of specifying Tag objects in the Pet.Tags array, you might instead just specify an array of Tag.Id values in Pet.Tags. Your client might then also request a list of all Tag objects and you would "join" the entities locally to display Tag Names. This might be a better approach if you already needed to download all the Tag objects anyway to support providing these tags for selection in a filter or some other process where all the tags needed to be available. Just because you can return a deeply denormalized view of the data doesn't mean it's either the "best" or most "efficient" approach.

One or More

The Pet entity has one or more PhotoUrls. In the OpenApi specification, the Pet photoUrls property is an array of strings. The requirement of at least one photoUrl is specified by component.schemas.Pet.required.photoUrls. The difference between this One or More relation and the Many to Many relation is that the PhotoUrl entities are not not related to more than one Pet entity.

Zero or More

The Category entity is related to zero or more Pet entities. Here is the component.schema.Category object specification:

namespace PetStoreSchema.Models
{
    using System = global::System;
     
    [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.3.3.0 (Newtonsoft.Json v12.0.0.2)")]
    public partial class Category 
    {
        [Newtonsoft.Json.JsonProperty("id", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public long Id { get; set; }
    
        [Newtonsoft.Json.JsonProperty("name", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        public string Name { get; set; }
    
        private System.Collections.Generic.IDictionary<string, object> _additionalProperties = new System.Collections.Generic.Dictionary<string, object>();
    
        [Newtonsoft.Json.JsonExtensionData]
        public System.Collections.Generic.IDictionary<string, object> AdditionalProperties
        {
            get { return _additionalProperties; }
            set { _additionalProperties = value; }
        }
    }
}

Enumeration

Enumerations are directly supported in the OpenApi specification. Consider the OpenApi PetStatus object:

       PetStatus:
            type: string
            description: pet status in the store
            enum:
            - available
            - pending
            - sold

And the generated class:

namespace PetStoreSchema.Models
{
    using System = global::System;
    /// 
pet status in the store
    [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.3.3.0 (Newtonsoft.Json v12.0.0.2)")]
    public enum PetStatus
    {
        [System.Runtime.Serialization.EnumMember(Value = @"available")]
        Available = 0,
    
        [System.Runtime.Serialization.EnumMember(Value = @"pending")]
        Pending = 1,
    
        [System.Runtime.Serialization.EnumMember(Value = @"sold")]
        Sold = 2,
    }
}

The Pet class Pet.PetStatus property is of type enum PetStatus. This property is stored as an integer in the Json format. Converting among the integer and enumeration representation is facilitated by the Newtonsoft annotation in the Pet class:

namespace PetStoreSchema.Models
{
    using System = global::System;

    [System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.3.3.0 (Newtonsoft.Json v12.0.0.2)")]
    public partial class Pet 
    {
        ...
        /// 
pet status in the store
        [Newtonsoft.Json.JsonProperty("petStatus", Required = Newtonsoft.Json.Required.DisallowNull, NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore)]
        [Newtonsoft.Json.JsonConverter(typeof(Newtonsoft.Json.Converters.StringEnumConverter))]
        public PetStatus PetStatus { get; set; }
        ...
    }
}

Summary

The OpenApi PetStore.yaml specification "only" defines the form of the data exchanged among the service and client. We derived an ER model from the OpenApi PetStore.yaml specification which describes the more general relations that exist among the application entities. The ER model contained "Many to Many", "One or More", "Zero or More", and "Enumeration" relations. We examined the the specific way each of these relations were satisfied in this OpenApi specification.

There are many ways the data in the underlying ER model could be exchanged among the service and client. The optimal form of this data should take is use-case specific.

The OpenApi PetStore.yaml specification is not optimized for a real client application. It was only designed to illustrate some of the ways relations might be denormalized for transfer among a service and client.