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.