I have recently joined a company where there are hundreds of schemas and thousands of tables. All the schemas we have are from other vendors who have allowed us access to their database. The entire database is poorly documented (little to no documentation). Vendors also don't care for documentation, hence they don't provide us with any documentation.

The other employees themselves find majority of their time figuring out what the data represents and if the data is current. There are numerous front end tools (not built by our company) which allow us to pull data (which is correct and relevant). However, then the issue becomes that we don't know the schemas, tables or columns the front end is using to pull the data.

I cannot create new tables as the tables are owned by 3rd parties who have allowed us access to their database. How can I go about documentation and how do I figure out what schemas, tables and columns front end tools are using to pull data.

Tables don't even have foreign keys to figure out how they are linking to other tables. For example in tableA there may be a column called car_id, but in tableB there is a column called cid (which is the car_id). They both link together.

  • 1
    This sounds like a failure of management and, unless that same management is serious about fixing the issue, you are investing time into something that will most likely never be recognized. If the internal development team is small, perhaps a Stack for Teams solution will allow for internal documentation and whatnot.
    – matigo
    May 5 '21 at 7:39
  • Hi, welcome to the forum! Also, welcome to our world! :-) Once worked for a company which provided an ERP solution to the company that ran the infrastructure for the rail network of a large European company. Early on in the job, I decided to look for FKs to get to know the system - my query returned nothing. I thought I was wrong so I asked the senior programmer - he said (with pride... no less!!!) that the system had no FKs... by design.... Literally, billions of widgets...Unfortunately, in the real world (™) this is the way things are... You could change job, but it'll likely be the same May 5 '21 at 8:17
  • Can you even implement FOREIGN KEYs? May 5 '21 at 9:09

To add on to the first two comments on your post, yes unfortunately this is the case in a lot of Vendor systems. Reverse engineering, and normalizing their less than optimal schemas has been a fairly common task I've dealt with for multiple Vendor systems at multiple different companies I've worked for. There's a couple things I recommend to make it slightly easier on yourself either now or at least in the future:

  1. Documenting your progression is important. As you find out the relations between entities, I'd recommend building out a database diagram for them, and then expanding on that diagram as you learn about other relevant entity relations. Of course in a large scale system, a single database diagram can become unwieldly, so you might want to break it up into multiple diagrams for each subject matter in the system. E.g. ERP systems typically have a multitude of modules that span different subject matters between Finance, Sales, Production, etc and in such a case I'd recommend at least a separate database diagram for each module.

  2. Familiarize yourself with a schema search tool, such as the Find DB Object feature of the Oracle SQL Developer or the search features of PL SQL Developer as mentioned in this StackOverflow answer. (Microsoft SQL Server has the luxury of RedGate's SQL Search tool, but unfortunately I don't think RedGate offers an equivalent for Oracle in their tool belt.) This will be helpful in understanding all the places an entity is referenced and how it's used.

  3. As much of a pain as it can be working with the Vendor and their documentation, if you have a technical contact who can at least answer the question if their application itself (or if they have a tool or database dictionary) that exposes the entity names of the fields behind the UI then that could make your job with reverse engineering their system a lot easier. Surprisingly this is fairly common practice that I've seen among many Vendor systems I've encountered. In some cases it was a dedicated shortcut key while focused on the field in the UI, sometimes it was a matter of right clicking the field and it was an option in the context menu, other times it was a separate tool bundled with their software (which was also used for customizations), and I've even seen it purely in the database in a dedicated schema / database dictionary. Also you may need to be a super user of sorts to be able to access that information. Either way, the Vendor should be able to answer that question for you.

Best of luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.