Database size spiralling out of control due to duplicate images

Question

Using Libreoffice Base with Firebird as a financial tracker. Originally had a ledger table, including a column for an image. For each entry, I would add the invoice associated with it. However, multiple rows could have the same invoice, and this is the root of the size issues; I could be adding the same invoice image ten times or more to the ledger table, needlessly inflating the size of the database.

I've looked into some solutions, and while dynamic images or linking external images into the database make sense from a file size and practicality perspective, it's a little messier in terms of portability, as I would prefer to keep things all as one file.

I'm currently looking into another alternative, where I have a separate table for invoices, and I simply refer to the appropriate invoice from the ledger table using relationships. I think this is where the 1:1 relationships come into play, but when I attempt to make one, I get an error about a "Foreign key"

At the moment, the table layouts are as follows:

Table: Ledger Columns: EntryID (Primary Key, Integer) | Invoice (Image) | Other unrelated columns

Table: Invoices Columns: InvoiceID (Primary Key, Integer) | Invoice (Image)

So how do I link the two Invoice columns to avoid having duplicate images?

Answer 1

You need to do the following:

• Add an InvoiceID column to ledger

  alter table "Ledger" add "InvoiceID" integer;
  

• Add a foreign key from Ledger.InvoiceID to Invoices.InvoiceID

  alter table "Ledger" 
    add constraint fk_ledger_invoice foreign key ("InvoiceID") 
      references "Invoices" ("InvoiceID");
  

• Make sure Invoices is populated, with the correct images, and that Ledger has the right reference to Invoices in InvoiceID

• Drop the Invoice (Image) column from Ledger

  alter table "Ledger" drop "Invoice (Image)";
  

• (optionally) Make InvoiceID in Ledger not null (assuming all ledgers have and need to have an invoice linked to it):

  alter table "Ledger" alter column "InvoiceID" set not null;
  

See also the Firebird 3.0 ALTER TABLE syntax.

Also, this is not a 1:1 relationship, but a 1:N relationship (one invoice can have multiple (N) ledger entries).