SAP – The Unrelational Database
I noticed within Microsoft’s SQL Server Management Studio that several of the main tables (ORDR, OWTR, etc.) seemed to have no foreign keys at all linking the tables. I was hoping that I could get a visual Entity Relationship Diagram of the tables somehow, so that I could see what I was working with and how the tables related to each other. Finding multiple threads on SCN that suggest that there is no such Diagram available to us (https://www.google.com/search?q=scn+sap+erd&ie=utf-8&oe=utf-8#q=scn+sap+erd+site:scn.sap.com) made me suspect that it’s possible that there IS no database relationship.
So, looking for alternative solutions to more easily try to create a visual ERD, I tried viewing the database’s relationships by using Visual Paradigm’s automatic DDL import tool (tutorial found at How to Reverse Database Schema into Entity Relationship Diagram without connecting to Database).
… and it presented to me the truth of what I always suspected – SAP’s database does NOT use SQL’s database structure elements at all to link the tables!
Note here the lack of ANY relationship whatsoever to the tables (no lines connecting them):
This can help explain the frustration that I and many others have had with trying to deal with SAP’s database structure – it simply must all be written into their code (their middleware, or API) that enforces what structure there may be. This is also most likely why it’s forbidden for us to directly update/add/remove any SAP records directly with SQL statements. It must all be done through the DI API (or the UI API) simply because there IS no database relationship. As there is no database relationship, if you affect one record, it won’t maintain referential integrity (https://en.wikipedia.org/wiki/Referential_integrity), and other data could get all messed up.
On one hand, I can understand where this type of non-structure would be beneficial to SAP, helping to keep its database and software proprietary and protected from people migrating away from it, or making third party solutions that could replace SAP’s software. On the other hand, as a programmer, it is infinitely frustrating to not be able to see how all of the tables inter-relate except through trial and error, or bugging people that have far more experience to see what THEY have uncovered through trial and error.
It is simply very inefficient for code development for these tables to not be related to each other through the database structure. This “pseudo-relational” database substantially increases the time it takes for software development, as the programmers spend substantial time “discovering” the way the database works.
Food for thought, from a relative newbie that’s just starting to “dig in” to this whole SAP world….
Hi Westley.
I read your blog... and I was wondering, why you spend the time on finding the relations with different Tools. First... If you open Managementstudio you will finde out within seconds... that there are no relations set like foreign keys, no special Index...etc... that's like it is.
First.. there is a godfather's question... why you like to have these? There are advantages but as well as many advantages you will get the same amount of disadvantages...
Second. Where is the way to believe ... that keys will implied a given structure on cases you like to update a record...????
third... where is the significance of manipulate a Vendor DB through non role based creativity? Especialy for person starting with B1. And let me say... I starred 6 Month ago programming in B1... and I'm really happy with the SDK, Help files... and as well the Help of the SCN community.
I think B1 is a very powerful Software with so so so many functionality... so many capabilities of different using and customizing functionalities... as well as so many different using of country specific inter gradient basements...
From my point of view... I know some other APIs, Documentations... SDKs... and I know manipulating DB with complete entities... But I'm really happy in B1 as it is... You know the way it has to work... and you know the ways you have not to work on...
I don't want to Change B1 it self I want to add functionality to it.. not more and not less. If I have to change B1... this might not be the correct software for the specific task...
Nad last but not least....
"Food for thought, from a relative newbie that's just starting to "dig in" to this whole SAP world...."
Newbies should be carefully at any time beginning programming new unknown software... especially ERP's... and in this case it is much better that is more difficult to start... as to work seriously ...
I think from my point, as I started with B1... there are lots of information on SCN... and I finde out the way it works very fast... and with Help form some really friendly community guys... I learned so many things in a very short period... Some times I thought why I should use direct SQL... and the way it has to work... is sometimes a bit messy and overloaded.. but it works for everybody... and this is completely okay to me...
and ... "it is infinitely frustrating to not be able to see how all of the tables inter-relate except through trial and error, or bugging people that have far more experience to see what THEY have uncovered through trial and error."
I gues most of it, I will finde within the SDK Help (if THEY will use it and spend time on reading).. As well If you use B1 Studio for some information... you see as well what's going on... and If you look through the SDK... and went through the functions... and library itself.. there you will finde more clearness as a Newbie...
Just my opinion and perception in this moment I read your article.
no hard feelings 😉
Oliver
I'm aware, and I did so at first. But I'm a visual person, so I like to see it displayed for me, that's just how I work. Also, I was curious if there were ANY relationships at all, and rather than checking on each individual table, one by one, the visual format let me view them all at the same time.
It's a basic database concept called Referential Integrity (https://en.wikipedia.org/wiki/Referential_integrity). If you have it, then the tables are truly relational. It prevents "orphaning" of records. If you need more explanations of why this is important, this link explains it better than I ever could:
http://www.odbms.org/wp-content/uploads/2005/11/007.02-Blaha-Referential-Integrity-Is-Important-For-Databases-November-2005.pdf
I'm sorry, but I feel something must be lost in the translation. I have no idea what you're saying here.
Those are good things, yes, but they don't make up for the lack of a true relational database structure that is transparent and visible to others. The metaphor that I would use is that we are blind, and are being guided through the world. Sure, our guide may be awesome, but wouldn't it just simply be better if we could see with our own eyes?
That's true, and I'm not denying it.
But that's the thing, we DON'T know, until we try, or until someone else tries. The documentation that exists is roughly a half decade out of date at least, and the examples don't even work with a current version of Visual Studio without substantial modifications. That is... frustrating.
Luckily or unluckily, I have no control over B1's direction and control, I just have to deal with it, and complain where I see issues. That is the only power I have.
I strongly disagree with this stance. An API should not be frustrating to use from the start, it should be easy to use, but difficult to master. In this case, the learning curve is too steep, in my opinion.
It is a great community, yes. But that doesn't make up for lack of official support.
There is a lot of information in there, but it is hardly easy to decipher when the tables and object relationships are not fully clear. Now if there was a master list associating all of the DI API objects, the UI API objects, the SAP tables, and their enumerations, then I might reconsider this. But until SAP actually releases this information in an easy to use format, instead of having to navigate through poorly documented CHM files (a format which hasn't been updated since 2001, over a DECADE, by the way), I'll continue to vent my frustrations as I have them.
Ditto.
Here's an example of what I'm talking about Oliver Buch, I just went looking to find out what table/column is affected by the oItems.Mainsupplier field. It's referenced in some of our code, but I wanted to be able to query the database directly to ensure that my code was changing this field correctly.
I look through the SDK documentation for Mainsupplier, and this is what I get:
Note the lack of a Field Name, while ItemType DOES state a field name.
So I click on the Mainsupplier object itself to see if that page helps me out:
NOPE, nada. I have to go to SCN and search for help from others who have run into this and who have found the answer through trial and error, because the documentation is incomplete and inconsistent: Import Business Partner Catalogue Numbers
This is the kind of thing I'm talking about, Oliver.
i cannot but agree with Westley Bennett.
relational integrity is necessary: it's normal and basic db design, b1 lacks it and query speed suffers from the lack of indexes.
knowledge of db structure and relations between tables is normal and helpful for developers. this is the first time in my programming experience i meet such an awful db design.
come on Oliver Buch we are facing a database with no E/R schema!
documentation is terrible, obsolete and often misleading. there are no official guidelines. code samples date 2005. com objects are not exactly a bleeding edge technology..
frustrating is a too kind word to describe working with these tools.
our only source of documentation is this forum, and is totally based on a trial and error process...
Hy Westly,
I see what you mean... I tried to explain what I mean... but this was to much text for a comment... Yes code samples are very old.. i agree ... VS2010 is not the best choice in 2016... Relations are important... I agree... but I'm okay with it like it is... I don't use the samples... just in the beginning... to see what's going on... and it was ok... I've seen much older stuff from others...
and I agree with christian ponzoni com objects... are not my favorite as well... but I have seen much poorer DB structures...
But I'm really not frustrated... at the moment I'm completely okay... thinks are working... and will wait for the next versions...
and understand what you mean...
Take care
Oliver
The logic behind all of it is that you are not allowed to change the database anyway, so all you could need them for is documentation. For all I've needed, documentation has been over 95% complete. And in the case of the "MainSupplier" some people just don't know where to look. The relationship of the MainSupplier field is documented in the database reference of the table where it belongs:
On the topic of COM Objects, I agree it's an outdated method. That's why SAP has invested heavily in a different platform and released SAP Business One on HANA, which includes the "Service Layer" component, which is much faster than DI API and completely OData/REST based. If you don't know it, check it out!
All the best,
Joerg.
I'm sorry Joerg, but unless my computer is messing up, or my eyes are playing tricks on me... I don't see "Mainsupplier" anywhere in your screen shot... Is it possibly in the line that looks like it's become a corrupted line of pixels in the row under ItemName?
No, indeed I referred to the "CardCode" field which is visible. While in the DI API the field is called "MainSupplier", in the database it is called "CardCode". You can find out the table and field names from the SAP B1 client application by enabling to "View system information" and then hovering your mouse over the field you are interested in.
Sorry I didn't mention it... most of the fields actually have the information in the DI API documentation, right in the piece of info that you copied earlier. Unfortunately, the MainSupplier field does not have the information documented, you would have to go through the client application in the way I mentioned to find it out.
Best regards,
Joerg.
I did as you suggested, and looked through the User Interface of SAP. I saw no field that was labelled "Main Supplier". However, I did find a field labelled "Preferred Vendor" that appears to point to OITM.CardCode:
But Joerg, do you see how someone with no previous experience in SAP could find this frustrating? While "Preferred Vendor" and "Main Supplier" are similar terms, they are not the SAME term. And to force end users to make a logical leap and/or assumption based around vague terms that are different, but similar.... do you see how this is problematic and not very good documentation?
Good documentation would mean that the UI hints, the object name in code, and the actual provided documentation in the SDK help files would ALL point to and use the EXACT same terminology for the exact same field. Anything otherwise is poor policy.
Now, as an experienced programmer, I'm sure that you've learned all of these ins and outs of SAP and have knowledge and experience on what to find and where, but can you see from the perspective of a newcomer how this amazing inconsistency throughout can be terribly frustrating, to say the least?
Hi Joerg,
It seems to me Westley described only a symptom in his original post, and I think christian ponzoni surmised the core problem perfectly:
(The code samples in fact date back to at least 2003, and probably even further back.)
and what that means is nicely underscored by these excerpts from this discussion thread from you and Westley:
The funny thing is, when I started developing B1 addons 11 years ago, the documentation was already equally lacking and uninformative.
Point being, it wouldn't hurt if SAP invested just a little in improving the documentation.
Regards,
Johan
Hi Johan,
I definitely agree on that...!
Best regards,
Joerg.