Introducing a new table between parent and child tables

Question

If I have a parent and a child table filled with data, is it trivial to add a new table between them?

For example, before introduction the relationship is:

Parent -> Child

Then:

Parent -> New Table -> Child

In this case I'm referring to SQLite3 so a Child in this schema has a Foreign Key which matches the Primary Key of the Parent Table.

Thanks!

Answer 1

This may be too obvious, but...

How trivial it is will be dependent on how much code has already been written that needs to change with this. If this is a new app, with little code written, and you're just beginning to work on the design, then yes it's trivial. If you have tons of functions (whether it's external code, or DB code like stored procedures and views) accessing these tables expecting the original relationship then it becomes less trivial.

Changing it in the database should be relatively non-trivial, assuming you know enough SQL to populate the new table and set up the relations.

As with all development challenges, you just need to look at what will be affected, how, and determine how you're going to account for those changes.

All of this is a really long-winded way of saying "it depends on your situaiton".

Answer 2

I am not disagreeing with David at all, just being precise re a couple of aspects of the change.

If you have implemented reasonable standards, then the only code affected with be code that addresses the changed columns in Child (not New_Table). If you have not, then an unknown amount of code, which should not need to change, will have to change.

The second consideration is the quality of the Primary Key in Child. If you have Natural Relational Keys, the addition of New_Table has less impact, not data changes required. If you have IDENTITY type keys, then you may need to reload, or worse, "re-factor" the keys.

Last, introducing New_Table is a correction to a Normalisation error, which is a good thing. Consequentially, certain Child.columns will become New_Table.columns, and New_Table can be loaded from the existing data. You need to do that correctly and completely, in order to realise the performance gain from the correction. That may mean changing a couple more code segments.

If you have ANSI SQL, all the tasks are fairly straight-forward and easy.