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).
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?