Normalization dilemma

May 29, 2010 at 00:42
filed under Dynamics AX
Tagged ,

Hi all!

I have a dilemma, and would like your opinions.
The situation is that I want to add fields to an existing table (say InventTable)

What would you prefer:
Add fields or table

So the question is, should I add new fields to the table (option one), or should I create a new table with the fields and link to it. (option 2)

Normally, option 1 is the standard way of adding extra fields to a table, but option 2 has a few advantages:

There are also a few disadvantages I can think of:

I would certainly go for option 1 when doing customizations for a customer, but would consider option 2 for product development. I’m in doubt. How about you?


RSS / trackback

  1. Kamalakannan

    You are right option 1 could come with the benefit of easy upgrade but i think apart from the choice of a customization/Product it also depends on the kind of table you have chosen.

    When you have table like “InventTable”/”InventTrans” I think it is better to go for Option 1. Because other than the form complexity you may also need to rewrite your select and query statements in several places. In fact we may end up modifying lot of forms also.

    When it is the “WMSTable” or “ProdBOM” table then i would go for option 2 because the scope is limited and so the customization.

    But as you began this post it is always a dilemma

  2. Klaas Deforche

    Thanks for your comment Kamalakannan.
    Maybe you’re right and you can do both at the same time depending on the table.

    I’m glad someone else is at least considering doing this.

  3. Kenny Saelen

    I would not go for option 2 just for the simple fact that it will confuse developers. It could become difficult to actually know which fields that are linked to an item are on what table…

  4. Kenny Saelen

    And not to forget, when using AIF you will need to add the linked tables to the services as well…

    And several forms will need extra datasource when displaying inventtable data in this case…

  5. Matt Vail

    It also depends on the type of fields you add to the table. For exemple if you add “memo” fields it might be wiser to go with Option 2. Most AX selects in code do not specify a list of field to fetch (select from inventTable) and you might not want to fetch “bigger” fields like memos in all those places.

    There are pros and cons for both. Personally I tend to go with Option 1 unless I’m adding memo fields. I want to avoid slowing down InventTable selects in areas I’m not concerned with.

  6. Frederic Rolland

    I believe the best is a mix of both according some rules.
    The idea is getting the best of both of course :-)

    I would go for option 1 when you have single fields strongly related functionaly to standard fields.

    I would go for option 2 when I need several fields (let’s say more than 3?) and/or when they are related to a brand new function.

  7. Luegisdorf

    Hi Klaas

    If you really only want to add fields to the InventTable, join option 1, because:
    – The table header should not be upgraded to your custom layer
    – AX Upgrade should be no problem, if you have your own name standards and the the table header isnt messed up (as describben below)
    – Much easier for development and even more clear in data model view

    The only restriction I can see:
    – ‘Delete table members’ on a custom system which already contains table members on the same layer you want to import

    And of course use option 2, if you want to add containers, blobs and other hughe sized data fields.


  8. Klaas Deforche

    Hi Patrick,

    Thanks for you reply.

    I asked this question because my company is starting doing product development for AX (as opposed to projects for one customer).

    The goal is to create a product that is maintainable in term s of upgrading.

    The problem I see when adding all your ‘AX product fields’ to existing tables is that you have a larger ‘footprint’ on standard AX.

    That way you’ll have a harder time upgrading from one version of AX to an other.

    You will also have to put a lot of effort into upgrading a customer where AX was already implemented en already contains customizations to the object.

    And, if you have 2 products, you will also have more trouble ‘merging’ them because they both do customizations on the same table.

    I’ve seen other AX partner do the second option, with success, but I’m still in doubt whether it’s a good idea.

    Thank you (and Matt) your insight regarding the large data fields, it’s indeed better to choose option two for those.