Modelling Master Data -> partially overriden: How can this be done?

Hi

I need help in modelling with Lightspeed.

I have a model for a product:

Product:

• ID
• Name
• Description
• Price
• 100 other fields

A Customer is allowed to OVERRIDE only two fields for his store:

• Description
• Price

I would like to store only the two overridden fields in a table for the customer and then fetch a product for a specific customer to avoid redundant data and to make sure, that always the master data is used, if the field is not overridden.

Product:

• ID
• Name (data from the master table)
• Description (overridden data)
• Price (overriden data)
• 100 other fields

How can I do this with lightspeed? If possible only with one intelligent product model.

Yes you could model this by using a Class Table Inheritance relationship, storing the modifications in a table which is derived from Product via CTI (e.g. CustomerProduct). You can have the relationship between Product and Customer on either Product or CustomerProduct depending on where it is most appropriate.

You will then want to have Price and Description as standard entity properties on the derived class, with your base class having the properties named something else e.g. BaseDescription, BasePrice and having the column names for those two properties set to the underlying columns of Description and Price. e.g.

You can set the generation on these to be field only if you want to avoid exposing these as properties on the class itself. You will then want to set up some manual properties named Description and Price in a partial class implementation for Product where you expose the standard property gettor/settor's to work with LightSpeed targetting the _baseDescription and _basePrice fields. Mark these as virtual so they can be overridden. e.g.

public virtual string Description
{
  get { return BaseDescription; }
  set { BaseDescription = value; }
}

public virtual string Price
{
  get { return BasePrice; }
  set { BasePrice = value; }
}

In your derived class of CustomerProduct set the generation mode for the two fields to be field only and then implement manual properties for these in a partial where you are going to clone the standard gettor/settor code but make a tweak where the value is null to return the base value.

e.g.

[System.Diagnostics.DebuggerNonUserCode]
public override string Description
{
  get { return Get(ref _description, "Description") ?? base.Description; }
  set { Set(ref _description, value, "Description"); }
}

[System.Diagnostics.DebuggerNonUserCode]
public override string Price
{
  get { return Get(ref _price, "Price") ?? base.Price; }
  set { Set(ref _price, value, "Price"); }
}

Jeremy

Hi Jeremy

Thanks for your helpful post. Unfortunatly it did not work. I did this exactly as you described.

        using (var unitOfWork = DBHelper.xyz.CreateUnitOfWork())
        {
            User user_loaded = (from u in unitOfWork.Users where u.Id == 7 select u).FirstOrDefault();

            var product = (from cp in unitOfWork.ProductOverrides
                      where cp.User == user_loaded &&
                            cp.Id == 3
                      select cp).FirstOrDefault();

            product.Price = 15;

            unitOfWork.SaveChanges();

        }

"Query Error: Could not find field [ProductType] on model [ProductOverride]"

I added a ProductType discriminator to the product table. But it still does not work.

Using a relationship, lead to the following error:

Invalid column name 'Description'

Invalid column name 'Price'.

Invalid column name 'UserId'.

in The SQL I don't see any joins.

Removing the relationship leads to: Invalid column name 'Description' Invalid column name 'Price'.

Do you have an other ways how this could be modelled? I think the proposed solution does not work in Lightspeed at all. Were you able to query an overriden product by customer? Is there a solution, that were the renaming of all base table attributes is not necessary? That would be great too.

Yes I was able to query the overridden product via the customer relationship. Make sure you are using Class Table Inheritance - you should be seeing a join between the base and derived table in the generated SQL. Also in your model above you have the base properties named the same as the derived version.

You will always have to have the fields in the base named differently from the derived version because LightSpeed using the backing fields for managing data. Because you want to have a value for the base and a value for the derived version we need separate storage for these hence the need for the fields to be distinct.

Jeremy