Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Unanswered: Help with master detail table design

    Hey fellows, hope you are all are good

    I have two tables in database with 1 to many relation (master/child). The tables already have a lot of record entries in them.

    Now it is identified that the master can repeat in some entries also ( so the master becomes another child table)

    In my discussion with manager we discussed two solutions

    • I proposed to make a new table and reference the previous master table's primary key in it
    • Manager insists on not creating a new table but to include a reference_id column in the existing master table and repeat the reference the new records in the same master table.


    I am totally confused on what to do here? I feel that referencing the existing master in a new table would be more beneficial and save us from redundancy ?

    What do you guys say ? Any convincing advise which I can get from you fellows and give to my manager ?

    Best regards, Mike.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not understand what you need to do. Can you elaborate? Please provide examples to illustrate your entire situation.

    Andy

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Dude, if you already have a PK in your Master table, then you can not have duplicate records in it.

    DDL for both tables would help.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Mar 2009
    Posts
    73
    Hi consider the following example, my advice was turned down eventually maybe you guys can advice me better. Check the images.
    Mike.
    Attached Thumbnails Attached Thumbnails 1.png   2.png   3.png  

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    So basically what your company want to do is take your current "Department" table and have a sub-department entity and the profit-detail needs to be under the sub-department, not the department like it is now.

    My question, is how do you know which profit-detail records go to which new sub_department?

    Andy

  6. #6
    Join Date
    Mar 2009
    Posts
    73
    Along with redundancy in similar rows of the same department with the 3rd approach, thats a good point you made.

    I was told to leave it up to the user to enter the reference_ID and in case he makes a typo the records wont go with the correct department. ( Sometimes convincing people is hard so I made a thread to get some good valid points)

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That was not my point. You will have sub_departments with whichever method you choose. The point I am trying to make is your current data. Right now you have profit_detail data that is pointing to, for example, the SALES department. What sub-department are you going to change the current profit-detail records?

    Andy

  8. #8
    Join Date
    Mar 2009
    Posts
    73
    Hi, Like currently

    for Sales department there are
    Hardware
    Computers
    Routers

    It is identified now that any department can have many new sub departments for new records

    So if there were a new sales department "Sales 2" we may have it as

    Sales "2" under a new category Sales

    So previous sales would become Sale1 logically so at report time the structure must be

    Sales
    Sale 1
    Computers
    Hardware
    Routers
    Sale 2
    (All the new details)

    Existing records wont change they will keep under the same but logically the table should accommodate new records under a new head.

    Mike.


    Quote Originally Posted by ARWinner View Post
    That was not my point. You will have sub_departments with whichever method you choose. The point I am trying to make is your current data. Right now you have profit_detail data that is pointing to, for example, the SALES department. What sub-department are you going to change the current profit-detail records?

    Andy

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    So what you are saying is that the profit-detail data will not change at all. You plan to just change the current department rows to reflect the sub-departments and add new rows for the remaining sub-departments.

    I tend to want to keep things as simple as possible, so I would have to go with you boss's idea by just adding a column that is the super-department. Your idea, although sound (and the way it probably should have been from the beginning) is not practical now because of the amount of maintenance it would require.

    Andy

  10. #10
    Join Date
    Mar 2009
    Posts
    73
    Yes but the existing table has only about 10 records now. I only wonder this idea would be fruitful on long term basis.

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How many rows will it contain when all of the sub-departments have been added? How many records are in the profit-detail table?

    Andy

Posting Permissions

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