I have recently come across a DB Schema without about 300 tables, none of which have any keys associated with them. What I did notice however is the schema has 3000 or so SProcs which is handling most of the validations.
Does anyone know of any advantages/disadvantages to this approach? I have always been around DB Engineers who have stressed upon having keys for Table level integrity.
Thanks for the reply... (the db designers are no longer present... they were contractors, before my time)
I thought this was extremely strange to not have any keys at the table level. I have worked with Oracle Databases in two other jobs and have been involved with Oracle schemas for about 8 years... and keys were a MUST. I was just unsure if I was missing some benefit I was unaware of... or something 'new' in RDBMS theory.
It scares me to think that the application sitting on top of this schema could malfunction and input data without any integrity constraints existing at the table level. This should be a fun one to support.
Most likely, this database was "designed" by VB or C developers who view databases as nothing more than glorified file cabinets. These people cannot conceive of placing any business logic in the data layer, insisting that all code should be in the middle tier or the interface. It can be hard to talk sense to them, but the best rule of thumb I've come up with is "If a rule applies to the data, keep the rule with the data."
I bet most of the procedures in your database are nothing more than get-n-puts that insert or retrieve records. Put there, no doubt, by people who had heard that user's should not have direct access to the data, but who have little understanding of why.
If it's not practically useful, then it's practically useless.