locked
thousands of tables in a database RRS feed

  • Question

  • Guys, I have a temp-to-perm offer for customizing and implimenting a vendor application that has thousands of database tables(7500 tables to be exact, of which half are audit tables. One audit table for each working table.)
     
    My first impression is that this is a very poor database design. Do you guys think the same way? I don't want to be too judgemental from the beginning. In your opinion, could there be any possible good reasons for such a database design?

     





    Tuesday, August 9, 2011 1:53 AM

Answers

  • I agree with Brent.

    Although 3750 tables (ignoring the audit tables) does seem like a high number, that's not the primary determinator of good or bad design. I would be looking closely at the relationships and the design. If the number of tables is justified by a complex data environment with that many entities, so be it. If that's true, that's a good reason to have that number of tables.

    If the proliferation of tables is the result of some misguided attempt normalize beyond 3rd normal form, that's a different matter.

    Ironically, it's more typical to find too few tables and lack of normalization is really bad designs.

    As you say you will not have the authority to alter the schema, isn't the question moot? Seems you just have to decide if you want to deal with it as-is.

     

    Tuesday, August 9, 2011 11:43 PM

All replies

  • The quantity of tables seems high, however ... if the working data is adhering to normalization guidlines and each working table represents a unique entity, then you may need all those tables.  Also, if many of the tables are merely domain tables (lists of available values in a field of a working data table), then again they may be needed.

    So .. the moral of the story is... see what each table is and what it represents, then decided if the structure is not correct.  But ... I have to ask, do you have the managerial direction to change the schema?  Or ... are you just tasked with implement a tested Front End application that uses a messy back end?


    Brent Spaulding | Access MVP
    Tuesday, August 9, 2011 4:52 AM
  • Thanks Brent. I don't think I will have the authorization to change the schema except for creating new stored procedures, funcations, etc to support custom reports. So I am basically going to have to implement the tested front end app with messy back end.

    Tuesday, August 9, 2011 5:03 AM
  • How does auditing implement? Triggers/Audit events? Does it hurt performance? I am almost sure that not all tables needed to be audited
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 9, 2011 7:37 AM
  • I agree with Brent.

    Although 3750 tables (ignoring the audit tables) does seem like a high number, that's not the primary determinator of good or bad design. I would be looking closely at the relationships and the design. If the number of tables is justified by a complex data environment with that many entities, so be it. If that's true, that's a good reason to have that number of tables.

    If the proliferation of tables is the result of some misguided attempt normalize beyond 3rd normal form, that's a different matter.

    Ironically, it's more typical to find too few tables and lack of normalization is really bad designs.

    As you say you will not have the authority to alter the schema, isn't the question moot? Seems you just have to decide if you want to deal with it as-is.

     

    Tuesday, August 9, 2011 11:43 PM