Hi Robert,

You need not 2 but 3  entities.

Product

• Id (primary key)
• Name
• ...

Category (static entity)

• Id  (primary key)
• Name
• Label
• ...

ProductCategory

• Id (primary key)
• ProductId
• CategoryId

On ProductCategory add an Index of ProductId,Category (unique).

Regards,

Daniel

Hello Robert, 

You should create a Many-To-Many table called ProductClassifications to store all the classifications of the product. This table should have foreign key reference to ProductId and ClassificationId.

Hi Robert,

sounds absolutely right to me.

I think the only decision really here, is whether the Classification table should be static or a real table.  That depends on 2 things

• how likely it will change (as in, your business grows and new categories are added)
• how much coding (for example of business rules) involves dealing with the actual values of your categories

For things that don't change often, and that you find yourself using actual values in the code, static is the way to go, examples are statusses, levels, ...

For things that are more likely to change along with changing business insights, such as re-arranging the shop floor into different categories, and are not coded against literally (e.g. you might want to filter for them on a given screen, but you don't have any business rules singling out specific values) i would rather go with a real entity.  And I think product categories are an example of this.

For things that are not clearly one of the above, you'll have to make a choice.  In such a case I would still go with the regular entity instead of the static entity, and just try to model into that entity the properties that I want to code against.  But it would be equally valid to choose for a static entity then.

To give an example of such a choice, let's say your product categories are likely to change regularly, as it is a young and growing business, ever re-assessing what type of things to sell, adding new product categories every quarter.  That would really be an indicator to make them a real entity, not a static entity, because you don't want to release new versions of the software every time a category is changed.  But let's suppose you have 3 categories that you don't want to sell to people under the age of 18.  In case of a static entity, you could just code a business rule saying "if category is XXX, YYY, or ZZZ, age of customer must be > 18.  I would still much rather do it as a regular entity, but add an "only_sell_to_over_18" indicator into the category table.

TL;DR yes, model with a junction entity, but think carefully if category is really a static entity.

Dorine

Hi Robert,

All that Dorine writes is true. What I want to add is you can start easy with a static entity if you think that that is enough for now. When later in time it turns out it needs to be a normal entity it can easily be converted without losing data.

Regards,

Daniel

Thanks all for your responses on this! Very helpful indeed. 

Okay, i will go down that route and see how i go. 

Cheers all,

Rob 

Hi All,

Sorry, just to confirm this is the right way of doing this. It's working, but just want to confirm it's moderately elegant (i changed the table names - just an example) and considered best practice.

1. Create tables:
   1. Person table - this is where i can add a name
   2. Description static entity table - this is where i have my descriptions
   3. PersonDescription - this is my junction table. I have a Is_Selected bool to capture where a checkbox is set
2. Create aggregate:
   
   
3. Display the aggregate values in the form and insert a checkbox for each
   
4. Loop through the aggregate and assign the values to PersonDescription
   
   
5. I then run this action at the save, after creating the Person entity.
   

Is this method best-practice?

OML attached.

Thanks

Rob

Hi Robert,

I must say, I have never tought of doing it like this.

Typically, the very existence of a PersonDescription record serves as the indicator that there is a relationship between that person and that description.  This means you only create records for those that are checked, and delete records for those that get unchecked during an update.

This makes for a little bit more work though, in developing the code :

* aggregate for retrieving current situation, will typically have a calculated boolean field, with a formula of something like PersonDescriptionId<>NullIdentifier()

* that's the boolean that you can put on your list or table widget as a checkbox

* save logic will involve going through the list, creating/updating a record when checkbox is checked only, and deleting record when checkbox is unchecked but record exists in database

So, a little less straightforward than just creating all possible combinations and having a boolean, but that solution would mean probably a quick bloating of your database with records that don't add any information.  Maybe you could get away with it if there are very limited possible descriptions, and on average people have maybe 80% of them checked.

Dorine