Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010

    Unanswered: Parent child table update (EMP and DEPT)

    Hello Friends,

    EMP is master table and DEPT is child table. One of the Oracle form is based on DEPT (child) table. My requirement was to delete some of the department permanently which means departments, their all existing employee and other data needs to be deleted permanently from database and also while entering data, we should not see those departments in future also.
    We went ahead and ran delete statement against the DEPT table (which is a child table) for specific departments and confirmed that all departments are gone from child table and master table as well.
    Now even after confirmation of record deletion, when we are running query on the form, we are seeing associated value for those departments. Im not able to figure out why?
    My questions are:
    - Did I make a mistake by deleting records from child?
    - Whatever I did was correct and there is some other loop hole.
    - Any other suggestion by which I can fix the issue.
    Any help or suggestion would be really appreciated.


  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    First off, it would seem your logic is backwards. Employees make up a department, departments do not make up employees. If DEPT is truly a child of EMP and you deleted a/some department(s) from DEPT and you deleted all employees who were members of those departments, then you should not be seeing any of those departments show up. Take a look at your form and ensure that these 2 tables are where the department column is being pulled from.

  3. #3
    Join Date
    May 2010
    Hi Dave,

    Thanks for looking into it. I was giving an example and here are the actual details:
    Tables 'A' columns are:
    Tables 'B' columns are:

    Form is based on table B which is a child table, while A is master table. On form we have:

    We have deleted categories from table b but their associated amounts are still being add up.
    Please let me know if any additional information is required.
    Thanks for help!

  4. #4
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    It is BAD design to have same data field (AMOUNT) exist in two tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by anacedent View Post
    It is BAD design to have same data field (AMOUNT) exist in two tables.
    taken at face value, this statement is ridiculous

    taken within the context of this thread, both tables easily can and possibly should have an amount column

    for the categories table, it would hold the category amount, while for the child customer table, it would hold the customer amount

    and please don't anyone suggest that the column name should be category_amount in the category table and customer_amount in the customer table

    of course, "amount" by itself is less than helpful, so the columns really should have better names | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5

    as of the "amount" column: if CUSTOMER table is a child of CATEGORIES table, then - if I understood what you said - sum of all customers' amount values per category makes the category amount. Something like this:
    update category a set
      a.amount = (select sum(u.amount)
                  from customer u
                  where u.category = a.category
    But, does it make sense? Why would we maintain that information in the CATEGORY table, when we can easily calculate it using information stored in the CUSTOMER table, any time we need it? (Presuming that data warehousing and similar is not what we discuss.)

    Perhaps I'm wrong (as well as Anacedent), but - could you explain what you meant by saying that it is OK to have the "amount" column in both tables?

    - o -

    As of the "original" problem: EMP/DEPT example was badly stated, that's for sure. Employees are children, departments are parents, not vice versa (as it has already being said).

    Nevertheless: I didn't understand how you deleted those records. Was it from the form, or ... SQL*Plus, perhaps? Whichever option you chose, the fact that you deleted those records doesn't mean that the "amount" column will automagically decrease in another table. (This is yet another example of why keeping that information in both tables is a bad idea - from my point of view). So, you'd either have to manually remove those amounts first and then delete whole records, or have a database trigger which will take care about it.

    Perhaps you should redesign that design, remove the "amount" column from the parent table and delete children as you wish. If you still insist on having an amount available all the time, at once, create a view (that would sum children amounts per parents).

Tags for this Thread

Posting Permissions

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