Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have scenario where there is Table T with columns PK,A, B, C,D

TABLE t ( PK, A, B, C, D )

i have to insert data from different sources ie columns A,B,C,D are calculated separately.. and they same primary PK.

I would like to ask if it better to create indivdiaul tables for each column and then join them using PK and insert into final table T

pk|A ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

or

it is ok to update ie

first have some thing like T (PK,A,B,C,D) DATA SOMETHING like (1,a1,NULL,NULL,NULL)

run update statement for all columns B,C,D sequentially and get all the data

T(1,a1,b1,c1,d1)

share|improve this question

1 Answer 1

I'm not sure either of these are really great options. Here's my thoughts:

pk|A ,pk|B ,PK|C ,PK|D ----> JOIN AND INSERT INTO TABLE T(pk,a,b,c,d)

With this scenario you have redundant data. Unless your individual PK tables are just staging tables, I think you could run into some serious issues here, especially with different consumers pulling data from different tables. I wouldn't chance that whatsoever with this design.

Not to mention that if PK:A, PK:B, PK:C, and PK:D are all 1:1 relationships, I don't see any normalization benefit out of this either.

T (PK,A,B,C,D) DATA SOMETHING like (1,a1,NULL,NULL,NULL) run update statement for all columns B,C,D sequentially and get all the data T(1,a1,b1,c1,d1)

I'm not sure I like this design either, although I'd put it at a higher preference then the former. The reason behind this is because what happens if business dictates that columns A, B, C, and/or D really aren't NULLable. Now you can run into a big problem of data conflicting with business constraints.

I think the real solution here may be to take a step back and analyze your data modification. Instead of having a step-based data insert, is there no way to do a single insert of all the required and necessary data into this table? In the application, retrieve all of the required data before each item's insert. That way you won't be defying any business rules, having redundant data, or additional operation overhead of the designs above.

In other words, if data columns A, B, and C are required, then you shouldn't be pushing that data out to this table until you have it. Without all of those required columns you wouldn't have compliant data, and it shouldn't live in that table until it is. And if D is nullable and optional data, then sure that's fine if it doesn't appear until a later time with a different DML operation.

share|improve this answer
    
Hi Thomas,Thanks for response.if the PK tables are just staging tables and customer is going to query the final table only. As you have pointed out that to do a single insert of all required data ,i was trying to the same using above mentioned staging tables,The real cause for physical table choice is the query which creates the data is dynamic sql and executed, so instead of i could make a CTE or derived table for all the four staging tables instead of physical table. Would like your comment about my inference –  sqlengineer Jan 23 at 14:06
    
I'd really have to see the data and your current process to make a comment on that. The ideal situation would be if there is a way to rework the logic to get that single insert. –  Thomas Stringer Jan 23 at 16:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.