Consider as child table or design as master table?

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  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

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  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"

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  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.