No! All invoices in one table! The second table contains the columns that are only relevant to accepted invoices.

The real question is: what is the business meaning of a rejected invoice?

Is this a real invoice which doesn't meet certain requirements? Or is there some form of staging process, which only "real" invoices pass?

Ask someone in the finance department, not a developer!

As you stated users need to see both accepted and rejected, I suspect these are all "real" invoices. Creating separate tables to store different states of the same entity is a bad idea.

I also suspect that some of the other tables you mention will be children of both rejected and accepted invoices. Having two parents for one table is a Very Bad Idea.

So I propose you have a single table to store all invoices. And a separate table to store the mandatory-if-accepted, invalid-if-rejected attributes.

1. The gain by placing the columns accepted invoices is in data quality. If you make one large table for everything, it's tricky to validate that the accepted columns are only set if it's an accepted invoice.

And storing accepted and rejected invoices in separate tables will cause endless headaches if:

- you have a table which is the child of both
- you need to assign gap-free invoice numbers across accepted and rejected invoices
- you do any other operations which apply to invoices of every type

2. Best is subjective. My suggestions are based on past experiences working on invoice processing applications. You may have completely different business requirements. You need to validate against what your business needs are.

Then yes, I'd definitely go with the approach I suggested.

The real problem is if you just have one big, fat table, then people can start entering values in the "accepted" columns for rejected invoices. Or not supplying mandatory info for accepted invoices.

You can get around this by adding check constraints. But this is a faff. Particularly with 60+ columns. And someone at some point will forget to update these constraints when adding a column.

With a separate table for the accepted columns, you can set these not null as needed. Avoiding the issues above.

As the comment below says, the tables should be the other way around.

The header table stores all the rejected and accepted invoices. The detail table only stores information about accepted invoices.

Not convinced on the attribute table, but I see what you're getting at.

1. The invoice table has the handful of that apply to both accepted and rejected invoices. This is the header. All invoices go in this.

2. This is the table with 60+ columns. Only accepted invoices go in this.

3. OK

4. You can switch the tables over as Racer described above. But you need to track down all the SQL which accesses the current table and check it still works. The longer your current process has been in place, the harder this will be.

You could swap out the table for a view which hides the join. You may need to build instead of insert trigger on it though to ensure all current DML works.

In which case to minimize risk it may be better to remove any not null constraints on the "accepted only" columns. And stick everything in one table.

True, though you're only talking 1 byte/null column that has a non-null after it. So you're only going to save huge amounts of space in extreme cases.

I think you're getting bogged down in your current naming scheme.

My proposal is your headers table now becomes two tables:

(invoice)_headers - this stores only the columns relevant to accepted & rejected invoices - ~10 columns
(invoice)_header_details - this holds the columns only relevant to accepted invoices - the other ~50 columns currently on headers.

The table product_services_details remains as-is. You could make this a child of header_details if you want to ensure this only stores rows linked to accepted invoices.

But if your database has been around for a while, this table split adds risk. You can minimize short-term risk by making any columns mandatory for accepted-only invoices nullable. Then stick rejected invoices in headers too. Doing this brings longer-term risks to data quality.

About the null columns where there are no following columns containing Non-null

Null columns at the end of the row (i.e. those with no non-null columns after them in that row) consume no space. So placing all the mandatory columns first in the table, like so:

create table some_table (
column1 int not null primary key,
nn_column2 varchar2(10) not null,
nn_column3 int not null,
nn_column4 int not null,
nn_column4 varchar(10) not null,
nullable_column1 int,
nullable_column2 int,
nullable_column3 int,
nullable_column4 int,
...
nullable_column65)

optimizes your storage. But usually you're talking about small gains. And adding a new mandatory column to the end of the table undoes your optimization.

So this is final-leg tuning. I wouldn't bother re-ordering an existing table's columns to do this except in extreme circumstances.

Indeed.