Best Practice for transaction tables in ERP AX 2012

Hi there,

I am very new to ERP Development, just want to have your opinions on create tables with best practices and forms for below.

I want to know for the last table “salesorderline” where I want to persist the final records so is it the best that I should have all the records saved(like copy) or just give a Foreign key link to the fields.

Can someone put more lights here about designing the tables and forms here, means what types of tables should I create and how to have records saved in “Salesorderline” table by getting column values from other tables.

Thanks.

Duplicating values is usually a bad thing, because you need extra work to keep copies in sync, which also increases risk of blocking, deadlocks etc. Or you forget to update one or more copies and end up with an inconsistent database.

A different thing is if you explicitly want a copy of the state at certain time. For example, you may want your SalesOrderLine to store the price that was valid when the order was created, even if it’s changed later. (Note that you can also handle this case by storing prices together with date periods of their validity.)

Note that these topics aren’t specific to Dynamics AX. You can find a plenty of sources on the internet on the topic of relational database design.

Hi Martin,

So how does it work in Dynamics AX, could you please put some lights here, means if I have to create the above tables, then what is the recommended and best way to accomplish this.

Thanks

Can you put some lights on this please.

As I explained above, the database design depends on the required business logic. There is no universal design for all possible (often contradicting) business requirements.

For example, if you want a copy of a certain value even if it changes later (see my example with price above), you must make a copy and store it somewhere. But when you want to always see the actual data (such as the current customer’s phone number), making a copy would be a mistake.