December 26th, 2012, 11:08 AM
Best practices for master data in ERP software
Are there any best practices or international standards for designing the master data model in ERP like systems ?
I'm talking about data like:
- goods/products index - with hierarchies, attributes, custom fields, elastic definitions of not indexed products
- configurable unit conversions
- packaging, containers, transport units - overall product logistics
- companies, people, institutions - and their roles
- logical and physical structure of an enterprise - companies, divisions, locations, areas, etc.
What ERP systems have the best master data models you've seen and can be treated as a good design reference ?
January 20th, 2013, 01:23 AM
As you know practice makes a man perfect that's why there is no alternative way without practise. You can learn by search on youtube or reading blog etc.
February 13th, 2013, 10:30 PM
I was involved in few ERP projects and from my experience how you store your date depends on many factors and there is no standard way of doing it. ERP software are usually huge software and many teams are involved when developing one so there will be difference in data structures even in the same project.
February 26th, 2013, 05:36 AM
I dont think there is any practices available for ERP.
June 26th, 2013, 06:58 AM
Extraction and Aggregation
Attribute Extraction and Enrichment
Final Duplicate Record Identification
June 26th, 2013, 08:47 PM
EDIT: After writing the post below I realized that I replied to a topic that was exhumed by a spam bot... (>.<)
In a word, no. And if there were "international standards" for this sort of thing they would be worthless without functional implementations. I'm sure there are probably several enormously costly ISO committees working such things as we speak, but that isn't any help to anyone since nobody implements ISO stuff anyway (unless the ISO standard is just a formalization of something that already exists in the wild).
I'm developing a system which covers everything you mentioned above among other things and I can tell you with confidence that there are no useful references on how to do this anywhere because the problem domains are so broad and the customer needs at any given time so specific (which is why I refuse to call my system "ERP" because the term is meaningless the way it is used today).
My code is not yet ready for open release, so I can't give you a complete schema rundown just yet (the API isn't finalized yet), but I can give you some general advice:
1- Stay away from frameworks -- in particular ORM systems are the devil.
2- Don't fall into the "visual development" trap. You should be thinking in the abstract about your data elements and then rendering them in your mind, then writing a schema. You will be juggling things far more complex than can be displayed on a screen and the screen will become a distraction and handicap before you realize it.
3- Program your database to act as a simple application server at first. That will keep your focus on solid data modeling long enough that you're likely to get it right. Later on you'll probably write an application server or use a web framework front end that can talk to your database or whatever, but initially you should focus on the db itself as much as possible.
4- When it comes to applications development over a non-trivial data model OOP programming practices are a good way to make your project indecipherable and fill it with heisenbugs. This applies to every element of your project that is not an interactive human interface. You need to keep your execution tail and the lifespan of state as short as possible unless your intent is to throw enormous wads of cash at extra people whose job it is to document and unmuck senseless inheritance models as they evolve for the life of the project. OOP is a big win when designing native application interfaces, though.
You need to model your data in a relational way and determine as highly normalized a schema as possible. This means, essentially, developing a self-consistent semantic system. To do that you have to answer some really basic philosophical questions like
Q: "What is a contract?"
A: A formalized agreement among parties -- any number of parties, not just two
Q: "What is a sale?"
A: An agreement to trade
Q: "Are sales and contracts the same?"
A: No. But a contract may govern a sale.
Q: "What is a trade?"
A: An agreement to swap lists of accountable things/actions/rights -- ...so...
Q: "What is an "accountable thing?"
Q: "What is the difference between a location and an address?"
A: A location is a place with its own natural structure, an address is a label we place on things we use to define relationships in space and time between ourselves and locations.
Q: "Should addresses be an attribute of a party/person?"
A: No, because parties, locations and the legal concept of address can exist independently of one another. Spans of time are the missing ingredient here, which means addresses represent a span-bound (temporary) relationship between parties and locations.
Other extremely useful questions:
-What is money?
-What is currency?
-What is an exchange rate?
-Is the system itself an actor, or does the system record activities external to itself? (in other words, is there an assumed default party the system represents?)
-Will your system need to mimic double-entry accounting or is a natural system of ownership/economic events suitable?
-If you need to mimic double-entry accounting do you have an audit requirement which forces the database to actually be designed that way, or is it acceptable to generate records from base facts when needed?
(The last two questions there are a big deal that will only become important to you once you start tackling accounting and inventory, and trying to define the differences between financial accounting and general accounting of inventory/time/resource.)
That sort of thinking led me to some unexpected answers that really helped my development effort. It takes considerable effort to think through things this way and then render actual schemas that comply with your revelations, but the end result is overwhelmingly worth the investment in time and effort.
The only way you can manage this in a sane way is to do it directly in a database. When it comes to power tools for doing this sort of work I haven't found anything better than using Postgres directly and writing the schemas myself in SQL (hint: if you haven't already spend some time learning how psql works). SQL itself has a few problems which get annoying, however, and doing a project of the magnitude I'm working on has led to me start working on an alternative data language within Postgres. But right now there is no better way to model a non-trivial schema than directly in SQL in a system that permits both creation of arbitrary rule systems and new data types like Postgres.
All the visual tools and frameworks are snakeoil -- development feels fast at the outset but very soon you run into situations the tools aren't designed to understand and you wind up developing the core data store of your project against the features in common between your framework or tool and whatever database backs it up -- and that's a huge handicap. You will also find that framework communities are full of people who don't know what they are doing and are attracted to frameworks as a sort of imaginary panacea -- which means that you can't find good advice there because its just the blind following the blind, whereas a database community itself is full of DBAs who are loaded with solid advice about data modeling -- and if you're doing a project from scratch alone this is a big deal.
This is getting long, so I'll stop now. This is a large area but its intensely interesting. At some point within the next two years I will probably be releasing our core schema sets publicly and then, for the first time, there might be a full-blown example of an ERP data schema and API out there. But I can't release it yet or it'll get FOSSilized and I'll lose my momentum.
Last edited by zxq9; June 26th, 2013 at 08:50 PM.