0

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!

  • Please provide us with your old and new table definitions - (using DDL - CREATE TABLE blah... - and DML (INSERT INTO blah VALUES (...)). That will give us a far better idea of what your asking! Also, knowing your server might be a help also! – Vérace Oct 4 '19 at 23:56
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.

  • Appreciate your understanding and advice. This was sort of my gut feeling. With that said, however, what are the implications of doing something like David is suggesting? Is it considered a bad practice to have a grandchild contain a foreign key to a grandparent? – karns Oct 10 '19 at 15:53
  • Also, to clarify, are you suggesting to try to remove project_id on batches, then? Then create an order to house all old data in order to be able to derive the project/company still? – karns Oct 10 '19 at 16:15
  • I wasn't talking about the schema, I was talking about the data model which comes first. As the data model suggests 3 entities - Projects, Orders, and Batches. A project may consist of of 1 or more orders, which in turn may consist of one or more batches. These should be your guide to creating these tables and relationships. Any attempt to 'get smart' and circumvent the data model relationships in any way, will result in grief and regret... I would also highly recommend that you reconsider using surrogate keys for your entities and look for the identifying attributes instead. Good luck! – SQLRaptor Oct 10 '19 at 19:06
  • So with that said, you'd suggest NOT adding Company as a parent to Orders? More specifically, adding company_id to the Orders table? – karns Oct 11 '19 at 12:16
  • sorry, I missed that last entity - you have 4 levels in the hierarchy, not 3 - Companies -> Projects -> Orders -> Batches. What are the identifying attribute of each of these entities? I mean the business keys - what tells them apart in the real world? If you provide these I'll answer with a full schema as I see it. – SQLRaptor Oct 11 '19 at 15:50
0

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)
)
  • Thanks for the response! However, most of the research I've done seems to indicate that this should be avoided. I'm struggling to understand the implications of doing so. – karns Oct 10 '19 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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