2

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
hiigaran
  • 285
  • 1
  • 10

1 Answers1

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).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Are those three SQL lines the same thing as defining the relationship graphically through the Relations screen? I have in the past linked Invoices.InvoiceID to Ledger.InvoiceID, and it showed a 1:n relationship line. Only, I wasn't sure where to proceed from there when making a graphical form. Would receive the error `*Column unknown *LINK_FROM_INVOICEID` when opening up the form. – hiigaran Oct 18 '22 at 11:19
  • @hiigaran I work directly with Firebird databases, I don't normally use LibreOffice Base, so I can't say if it is the same thing (though I expect it is). The last part of your comment sounds like an entirely different question, that would require a clear [mre] to be answered. – Mark Rotteveel Oct 18 '22 at 11:34
  • No worries. At least I've confirmed that I am indeed meant to use relations. I'll try to continue on my own from here, and see if I can figure out the rest myself. Thank you. – hiigaran Oct 19 '22 at 23:28