For entities with common columns, which better to process separately or centrally

Question

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

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"?