0

I'll try to be brief and clear.

I want a table called "Payments" tied to multiple tables representing multiple types of services offered by a company. It's fairly simple to add a "Payment" foreign key to each of the service tables and link them to the primary key in "Payments", but the problem is a practical one:

I'd like to create a (Libre Office Base) form that adds a payment. But this would require not only doing INSERTs into multiple tables ("Payments" for the info and the given "Service" table for the foreign key), but to add the primary key to a foreign table before the form itself creates that very primary key. So this option seems out.

I attempted to do this the other way - putting a foreign key "Service" into the "Payments" table and insert the IDs (primary keys) from the other tables there, but said primary keys are simple integers and adding say ServiceA's ID = 1 would fool the database into thinking that the ID = 1 object from all the other tables was "paid" as well. I know I could differentiate the IDs between these tables by making them varchars rather than integers, but then I don't think there's a way to make them autofills, right?

Finally, I thought that maybe a "category" option would help - choose from the type of service (for example "Surgery", "Drug sale", "Exam") and then type one of the IDs. That could give me the option to separate the same ID numbers in queries. However, this wouldn't solve the problem that attempting to insert ID = 40 from, say, "Surgeries", while any of the other tables had no such ID, would throw an apparent error, a relationship violation, since the database would think that entry is leading to nowhere.

I'm really struggling to find a simple solution out of this mess. I think the option most fitting for me would be to somehow make autofill primary keys that differ between the tables, but at this point I'll take any suggestion. Is it possible at all in Libre Office Base to create a form that would somehow fit what I want?

eworm
  • 27
  • 6

2 Answers2

0

Try to use trigger, after the primary was inserted the trigger will be called and the other info will be add to the other tables.

Cristian
  • 37
  • 1
  • 8
  • If I understand right, I should go with option 1 (a foreign key from "Payments" in each of the "Service" records) as far as structure is concerned. Then create a trigger that activates upon me hitting the "save" button in the form, but before inputting the data (reference key) into the right "Service" table. Said trigger would then create a record in the "Payments" table that would then be referenced in the new "Service" record. Am I getting this right? Question is, how do I make the trigger recognize which "Service" table it should add the reference in? – eworm Oct 23 '18 at 08:42
  • This cructure option also makes it impossible to split the payment for a service into two or more payments, which is an option I'd like to accomodate. I really think referencing the foreign "Service" in a given "Payment" would be better. – eworm Oct 23 '18 at 08:58
  • If you want to use the trigger method you need to make the referencing to foreign key in payment for the service, if i understand right. – Cristian Oct 23 '18 at 11:17
0

I think I've found a way to do exactly what I need.

First, I've created a table called "For" with only one field - an auto_incrementing BIGINT ID. Then, I created a relationship between this table and the IDs of my "services" tables (now also BIGINTs, obviously). With this, they now all have auto-incrementing IDs (a foreign key from "For"), but not a single one is repeated between them. There's no risk of my database being confused as to which table the ID belongs to.

Next, I've created a table called "PS" (for "Payments&Services") to act as an "in-between" table in a multi-multi relationship. I want people to pay for many services with one payment if they want to, but also to let them pay for a single service with multiple smaller payments. Thus, the table "For" has a field for a foreign key from "Payments" (the ID) and the foreign key from "For" (aka IDs from Services).

It sounds like a mess perhaps, but I think this is the best possible solution.

Now I'm having some other trouble with the "add payment" form I'm trying to make, so... See you one question over, I guess ;)

eworm
  • 27
  • 6
  • Spoke too soon. While this does technically help my issue, I now find myself unable to add a record to any of the "Service" tables. I have to create a new "For" ID and reference it as the "ID" in the given table - but it's impossible to add records to two new tables at once. I thought I could just "fill" the "For" table with integers and have the "Service" tables automatically draw a free number from that pull with each record being created, but I'm not sure how to do even that. – eworm Oct 25 '18 at 11:39