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.

I do not understand what you need to do. Can you elaborate? Please provide examples to illustrate your entire situation.

Andy

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.

Hi consider the following example, my advice was turned down eventually maybe you guys can advice me better. Check the images.
Mike.

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

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)

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

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.

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

Yes but the existing table has only about 10 records now. I only wonder this idea would be fruitful on long term basis.

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