Post reply

Consider as child table or design as master table?

  • June 21, 2010 at 5:17 am

    #236800

    Hi,

    I have a table where the masters information is available.

    we got a new requirement. as we cannot add columns to the existing tables, ( the reason is they used 'select * from tblname' in many places in the application and our scope is limited to that code.)

    i'm trying to do as a separate table. but this is also master information only. if i mention the primary key as the same key from the original table then, in this case i cannot enforce the consistency between the two tables.

    if I don't mention as primary key then redundancy will occur.

    so I made the PK-FK relationship between the tables and in the child table i made the key column as unique. so redundancy is nullified.

    is the above approach is correct?

    any input, design suggestions are welcome.

    hope I was clear in my statements...

    thanks,

    Ami

  • June 21, 2010 at 5:23 am

    #1182660

    Hi

    Going by the constraints that you are working with this seems a good design IMHO.

    Check the join conditions between the 2 master tables and also the indexes in the new table.

    "Keep Trying"

  • June 23, 2010 at 6:43 am

    #1183891

    OR...

    1- Rename your master table as TablenameBase

    2- Create a view Tablename (same name as the original table) so all current queries could continue doing those ugly "select *")

    3- Add new columns to TablenameBase

    4- Instruct developers to hit new TablenameBase from now on -- and never ever code a "select *" again.

    This solution will keep database architecture sound - from the data modeling/physical implementation point of view and, provides a tool to force developers to write better code in the future.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply