Adding new related table and associating historical FKs in other table

[TJRTech]

Okay, our data model has changed slightly and we are introducing a new table, NEW_TABLE, with PK/Identity column. This table will be associated with an existing table, LEGACY_TABLE, via a FK which is nullable (nullable so that we can time the rollout of app changes with in a lax fashion).

As part of the db change script we would like to create NEW_TABLE, alter LEGACY_TABLE to add the FK, add the FK constraint, and insert all the records required into NEW_TABLE and update the the FKs in EXISTING_TABLE in order to link up with LEGACY_TABLE.

I would rather not do this via a cursor, but can't think of any other way to insert the records into NEW_TABLE and then assign the FKs in LEGACY_TABLE.

Thoughts?

TR

 

[vongrunt]

Is this part of normalization (some columns from new_table currently exist in legacy_table) or you add FK/new_table completely from scratch?

 

[TJRTech]

If I understand the question, we are adding new stuff completely from scratch.

We are introducing a new entity, which is modeled as NEW_TABLE, and that new entity is associated with LEGACY_TABLE such that EACH and EVERY record in LEGACY_TABLE will be assigned on FK into NEW_TABLE.

So, to "prefill" the new FK columns for all the existing records in LEGACY_TABLE we will generated a number of NEW_TABLE records.

As it will work out, based on information already in LEGACY_TABLE, we will be "grouping" several LEGACY_TABLE records together through an association with a specific FK into the NEW_TABLE.

So, in a way, we are taking several existing attributes of LEGACY_TABLE and generating a new associated entity from them.

TR

 

[vongrunt]

Perhaps I didn't understand the question... what about something like:

insert into new_table (existing_attributes)
select distinct existing_attributes from legacy_table
where condition_to_remove_trash

update A
set fk=B.identity_primary_key
from legacy_table A 
inner join new_table B on A.existing_attributes = B.existing_attributes

 

[TJRTech]

Nice try, Von, but I think we are moving away from the new_table approach completely. It is NOT the case that the new and legacy tables will have the same attributes.

A little more info: Legacy_table has a list of events that are timestamped. Based on business rules, we want to "group together" or "associate" severa records in legacy_table based on their attributes, such that they are all associated under some new grouping...but not by the attributes themselves. The problem is that the attributes are fuzzy. One is a "period of time in hours", the others are attributes that indicate the action on the same foo by the same bar (sorry for being cryptic).

What we have done is to figure out a way with a sub-select to get the earliest time-stamp of all associated events, and use that as a new attribute of each of the events. It does what we need.

Again, sorry for being cryptic.

Regards,
TR