Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Location
    NH
    Posts
    3

    Unanswered: Enterprise DB w/out keys

    Hi all-

    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 your time.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The primary benefit of this approach to database design is job stability. The database only exists in your head, so they can never fire you.

    The disadvantage is that no sane person would ever let you near another project again.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    NH
    Posts
    3
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have your job cut out for you.

    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, you should have a blog, or at least a web site

    you can write (and one cannot say this about most of the blogs out there -- especially sql blogs)

    it's hard work skimming through various forums for your occasional gems

    put them out on the web for all to see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Aw shucks, Rudy...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •