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?Edit: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.   
A large Oracle database with no foreign keys
database design;oracle
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.