Solutions for Introducing a new Entity In Between An Existing Association

Question

I'm looking for best practices in regards to a specific change in schema. I'm using sql.

I had the following entities: Project and batches - a project had many batches. The batches table had a project_id.

As time went on, we decided that a Project would have orders which in turn would house those same batches. So now a project has many orders and orders have many batches. With that said, batches now have an order_id and project_id. My issue is this - the app depends on the presence of both fields, older batches don't have order_id and new batches don't have project_id so I'm getting exceptions throughout the application. I was thinking about getting rid of the project_id field on batches, but we need to keep the reference. We cannot create orders for each of those batches and migrate the field with a script since those orders will not be able to be completed/invoiced/etc (they already were in the real world).

Old Schema

create table Company
(
  id int not null primary key
)

create table Project 
(
  id int not null primary key,
  company_id int not null references Company
)

create table Batch
(
  id int not null, 
  project_id int not null references Project
)

My current schema:

create table Company
(
  id int not null primary key
)

create table Project 
(
  id int not null primary key,
  company_id int not null references Company
)

create table Orders
(
  id int not null,
  project_id int not null references Project, 
  po_number int
)

create table Batch
(
  id int not null, 
  order_id int not null references Order
  project_id int references Project
)

I would have to add a lot of conditional logic in the application in order to handle all of the cases, which I certainly don't want to do.

• What are best practices or good solutions for situations like this? Perhaps adding application logic is the only way to go.
• Should I find a way to remove project_id entirely? Then how would I make sense of getting order updated appropriately?

Thanks!

Answer 1

For such a structural change in your data model you will have to go “all in”. You can’t model the same entities in 2 ways. As you correctly observed, it will lead to endless grief for years.

That means your “old” view of the world will have to adjust. Have you considered creating a single order to contain all batches for existing projects, and name it “Migration Order” or something meaningful? Your application may choose to ignore it when presenting, or not. This will maintain a unified hierarchy.

Answer 2

I was thinking about getting rid of the project_id field on batches, but we need to keep the reference.

Don't do that. It's useful keep the "grandparent" key in the foreign key. That way you can get all the batches for a Project without JOINing the intermediate Order table.

eg:

create table Project 
(
  ProjectID int not null primary key
)

create table Orders
(
  ProjectID int not null references Project, 
  OrderID int not null,
  constraint pk_Orders primary key (ProjectID, BatchID)
)

create table Batch
(
  ProjectID int not null, 
  OrderID int not null,
  BatchID int not null,
  constraint pk_Batch primary key (ProjectID, BatchID, OrderID),
  constraint fk_batchOrder 
     foreign key (ProjectID, OrderID) references Orders(ProjectID, BatchID)
)