We all know that there are many types of invoices

Take the invoice for my environment as an example

Invoices include VAT invoices, which are divided into general invoices and special invoices

Both general and special invoices have columns such as invoice code, invoice number, invoice date, check code, unit price, total amount, etc.

In addition, the special invoices has tax exemption, address, phone, bank account

In addition, there are also travel invoices

Travel invoices can be simply divided into the following categories

Air tickets, train tickets, bus tickets, ferry tickets, online booking tickets, taxi tickets

All invoices have the following columns Departure time, Departure place, Arrival time, Arrival place, Amount

Airplane invoices and online car-hailing invoices have an invoicing date

Except for train invoices, other invoices have invoice number, and air tickets have invoice code

Air tickets have tax amount, aircraft invoice number, service company

Train tickets have a train ticket number

Online booking tickets and taxi tickets have their own ticket numbers

There is also a type of invoice called a fixed invoice

This type of invoice has amount, invoicing time, and its own invoicing number

The toll invoice is a fixed invoice, but it has entrance and exit and departure time

I know there might be some confusion and impatience when you see this -- 'How could it be so troublesome! '

But that's not the point, it's history for me

What I want to ask is, when a scenario like the above--"they are all called Invoices, but they are all different, no single Invoice can be fully used as the base class for all Invoices"--how should I store it?

I have tried to store them separately, and the result is that we have ten tables with different invoices, and most of them have the same fields. Such a table design brings great trouble to other members' development, so that we have to Spend a week standing still and finally refactoring the database

Putting things together without thinking is really convenient for storage and saves trouble, but is it necessary to do this for all such scenarios?

I want to know the boundaries of this question

How should I design the tables of the database for different entities that have common columns and cannot extract non-abstract base classes

  • (Americans only know of "VAT" as a large container of some kind of liquid.)
    – Rick James
    Commented Apr 17, 2022 at 5:31

1 Answer 1


Not an Answer, but some tips:

  • There may not be an obvious answer as to whether to have 10 tables, each with specific columns; versus 1 table with lots of nullable columns.
  • Or a compromise -- 3 tables, each with a few nullable columns.
  • Sketching out the Inserts, Selects, etc will give you a lot of insight into whether the SQL is significantly messier with one approach versus another. (You will need some Joins; that's OK.)
  • Database tables don't work well with "inheritance" or "subclassing", so try to avoid such.
  • Sometimes it helps to have an 11th table -- an Invoices table with very few columns. (I doubt if it will work well here.)
  • Will there be an "Entity" that involves several "invoices"?
  • Yes, each table does correspond to an entity. Also, once the table merge behavior has started, it cannot be stopped until a table is merged. Although the travel invoices can be combined into one table, the combined travel invoices are only two columns away from the VAT general invoices, so the VAT is merged The same also happens on the toll invoice for passing bridges. It has the time and place, so it is merged with the travel invoice, and the cost is only 3 columns. This way the fixed invoices are also combined, and finally, all invoices are combined
    – Ice_Wift
    Commented Apr 18, 2022 at 3:35
  • I've also tried minimizing storage, but I've had to compromise with my peers, which makes development more tedious.
    – Ice_Wift
    Commented Apr 18, 2022 at 3:36
  • @Ice_Wift - How many queries are involved? "Cannot be stopped" -- meaning that they are part of the same "transaction"? How many minutes does the process take?
    – Rick James
    Commented Apr 18, 2022 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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