Query on Ref Data and Master Data

[Former Member]

Hi,

I have a MDM design query.From implementation point of view,Master Data should be stored in Main tables and any dependent lookups in lookup tables(either flat,hierarchial etc).

Now in a scenario where both reference data(account codes,location codes etc) and master data(like Vendor ,Product etc) has to be centralized in MDM,are there any design principles to check whether to implement reference Data in Main or flat table(apart from number of records,and other optimizations that come with a MAIN vs look up table).CRUD operations also needs to be done in reference data.

It may so happen,that if a reference type data is implemented as Main table,it would semantically convey different messages to business and MDM stewards.

Please clarify,

Regards,

Premjit

[Former Member]

Hi Premjit,

I would like to add here following points:

1. When you are going to implement two master's like Vendor and Product in a single repository. Reference table makes sense. You should have common reference table to both these master's (like account codes,location codes etc) otherwise creating separate repositories one for Vendor and other for Product makes sense.

2. Every field value in repository occupies certain amount of space(memory say 4 bytes per field) in underlying database.

So for reference table it always make sense if you use lookup tables concept (a field in main table lookup to Account) rather than directly a field in main table. For example (For reference table Account codes, I have 100 values)

In main table if I have 1 lac records and one field is Account code and I create lookup field account codes so in this way in my underlying database only 100 values will be saved and thus memory for only 100 values(100*4 bytes) will be taken into consideration. But if i don't create reference as lookup table and create rather a field of Type Text Account code. So for each record in main table ( as we have 1 lac records, so 1 lac values for each record Account Code will be filled (1lac *4 bytes) rather than just 100). So in this way memory consumption would be quite more and thus always make sense to create reference table as separate lookup table when performance and optimization of repository taken into consideration.

Note: Above points are based on my opinions, I mean I think in this way. If I would be in your place i took above points into consideration and will go for reference table as separate lookup table and thus will create a lookup field in main table which will lookup to reference table(lookup table) rather than creating a field of Type Text in main table.

You can decide accordingly as per your business requirement and feasibility of solution.

Regards,

Mandeep Saini

[Former Member]

Hi Mandeep/Adrivit,

very good suggestions given by both of you. Can you please let me know the field type wise data storage details?

I mean any link or document where I can read these details and may plan accordingly in future.

Thanks & Regards

Rahul

[Former Member]

Hello Rahul,

Please refer below thread, which may add some more points in your decision.

[Former Member]

i understand ur doubt and hence believe i should not try to explain main tables / look up tables and what they should contain.

ihence would give u the pointer that would help you think in the right direction.

what is ur leading main table entry and what are ref table entry largely should depend on the scenario as well !!!

how ?

if u have a product scenario in mind - then products/items constitute main table and say Vendors and Customers are ref table

but when u have a Customer / Vendor consolidation sceanrio - u need to have them in the main table instead than in ref tables

hope i have made the idea clear !

now - in scenarios you could use multiple main tables (7.1 onwards)

as long as u can maintain them later as u rightly said (CRUD/interpretation to stewards) go ahead and create multiple main tables

so keep the sceanrio in mind - and decide accordingly.

thanks

-Adrivit

if theres any specific scenario in mind let us know - we can give suggestions