I am struggling to understand the design of an Oracle database that I have been working with for the past 9 months. The database is for our business management system and I work with it mostly for reporting and data warehousing.

The database has just over 5,000 tables (not all of them are being used) and so when I started working with it I had trouble understanding the relationship between different tables and finding the ones that were important to me. To make it worse, there are no foreign key relationships. At least, I'm yet to see one after 9 months. Additionally, the same fields are repeated in more than one table. For example, both the customer order line table and the customer invoice table have the fields order, line, and release and these fields are part of the primary key in both cases. Couldn't this have been managed by a relationship? I guess the one plus side to that is that I can query for the invoice information using the customer order data without using any joins.

I have also been recently been reviewing the functionality in some of the packages because we want to use some of the procedures for some custom tasks that we want to add. While reviewing, I found that cascade updates and default values are being managed in pl/sql code. Again, this can be done just by defining it as a part of the schema, but I even struggle to call it a cascade update because in the particular case I am referring to, it wouldn't be even be required if they didn't have repeated information in different tables. As for the default values, the UI for the system allows certain users to change default values and this approach avoids having to alter the table.

Now, this is from a large, successful vendor with many customers. I struggle to understand why these choose this design. I didn't go to school for computer science and I don't have enough experience to pass strong judgement on their design, so I keep telling myself that this is just some esoteric technique that I haven't been made privy to. I would really love to understand, why?


Just to clarify my comment above about cascade update. It's a bad practice and I don't think Oracle even has cascade update. But I didn't mean a cascade update in the true sense of the word. It was the update of duplicate information across multiple tables.

I must say though, despite the lack referential constraints, the data integrity of the application is robust and trustworthy (they have a ton of pl/sql code to ensure this). I mean these guys aren't amateurs (setting aside what most would call bad design). This I was just curious to know if there was something I was missing. But as people it the comments have stated, I may never know why they did what they did and how decisions made 20 years ago are affecting their architecture now. All I know is that they have a product that works and sells.

  • 5
    Any chance you can run like hell? Jun 4 '16 at 1:13
  • 5
    On a more serious note, there isn't any possible way this question is answerable without knowing something more about the vendor or the table design. Jun 4 '16 at 1:13
  • I wish I could tell you more. I'm not sure what to say. This is my first experience I have working with a serious database. Before this, I had only made Microsoft Access databases with forms on top of them. I just don't have the DBA vernacular to concisely explain the design -- especially one that I can't make sense of.
    – jaromey
    Jun 4 '16 at 1:28
  • 1
    This sounds a lot like an ERP system like (for example) Microsoft's Navision. They have the nice option to change the primary key (No.) of the product data which then is cascaded through all tables that reference those products. (Which can be millions of rows for products that are old and have a lot of orders, invoices etc). Products like this have a lot of 'history', decisions made sometimes 25 years ago and just nobody ever wanted to change that. Also some older versions really don't have any other option for external access but directly query the database. Jun 4 '16 at 7:28
  • 1
    As Robert said, there's no way we can tell what the designers of this system were thinking when they chose to do it this way (or if they even chose it consciously).
    – Ixrec
    Jun 4 '16 at 12:28

It sounds like you are digging around in the implementation details of a large product that (probably) is designed to be used via GUI's and APIs.

In your position, I would be asking myself why I was doing this. Probably you would be better off not >>trying<< to understand. If you really need to know this kind of thing, then you are probably trying to use the product the wrong way ... or it is not suitable for your needs.

... I keep telling myself that this is just some esoteric technique that I haven't been made privy to. I would really love to understand, why?

Well, yea, that is possible. It could also just be the result of poor design, or of the accretion of features.

Anyhow, I think your best bet would be to ask the vendor for more information. The vendor is under no obligation to explain to you these things, but they may be able to help anyway.

Note that some representative of your organization may well have signed an agreement with the vendor that >>forbids<< reverse engineering of the product. You might want to check that, if you / your management haven't already done so.

  • i tend to agree with you to some degree but not entirely. The reason why I noticed what I found to be a strange design in the first place, is because I am writing queries for reporting purposes and so that we can have insight into our business performance. Therefore, I needed to know what tables I need to be querying against. And the reason why I was looking at their procedures is because they allow us to build in custom functionality, which we are taking advantage of. In doing so, I would like to use their procedures where applicable.
    – jaromey
    Jun 4 '16 at 2:01
  • That was in response to the first part of your answer. The part before the edit.
    – jaromey
    Jun 4 '16 at 2:06
  • 1
    "The reason why I noticed what I found to be a strange design in the first place, is because I am writing queries for reporting purpose" - Is that how the product is >designed< to be used? Does the vendor documentation say "query the database directly"? I suspect not. Therefore my suggestion that you are using it the wrong way and/or it is the wrong product.
    – Stephen C
    Jun 4 '16 at 2:18
  • Actually, we are allowed to query the database directly. We can pretty much do whatever we want as long as we do not modify what they have done. There are other customers that we are in contact with who also do the same. The pre-built reports do not satisfy our needs, neither could we really expect it to for reporting purposes. As a business we need to be able to ask any question that our data can provide and no one knows our processes like we do. But I think we are beginning to stray a little here.
    – jaromey
    Jun 4 '16 at 3:15
  • Probably you would be better off not >>trying<< to understand Oh, yeah. Once I quit struggling to understand bewildering aspects of our code design it got easier to deal with. Fewer headaches and less frustration, et cetera really did improve my ability to work with the code. Over time, I found some of it is quite impressive. Eventually you realize one cannot explain stupid. So once you suspect there is no pony under that pile of shit you just quit trying altogether and refactor and re-code as time and opportunity allow. And find peace through acceptance of the rest.
    – radarbob
    Jun 4 '16 at 4:32

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