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….