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



  • 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


  • QA Engineer

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

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2