0

I have an application that mostly is composed of forms that create, modify, read, and deletes tables and establish relationships. I use SQLAlchemy. If, for example, I need to create an invoice and associate it to a customer and to an agent, from the salespeople of the company I use the following pattern:

1: Load mappings:

agent_name_id_map = {r.name, r.id for r in session.query(Agent)}
customer_name_id_map = {r.name, r.id for r in session.query(Customer)}

2: Build the invoice form, which, among other things, has two comboboxes, like:

agent_combo = ComboBox()
agent_combo.addItems(agent_name_id_map.keys())

customer_combo = ComboBox()
customer_combo.addItems(customer_name_id_map.keys())

3: The user wants to commit the form and after some validation, I execute, say, the save method:

def save(self):
    invoice = Invoice()
    ···
    invoice.agent_id = agent_name_id_map[agent_combo.getText()]
    invoice.customer_id = customer_name_id_map[customer_combo.getText()]
    ···
    session.add(invoice) 
    session.commit()

The code is like pseudocode. In the real app, I handle some exceptions, make validation, etc.

Is this a good mechanic for doing this?

These kinds of relationships are spread over the whole application, linking payments, expenses, documents, agents, partners, etc. I am not sure if I am doing well.

lennon310
  • 3,132
  • 6
  • 16
  • 33
  • You didn't mention which data store you use, but if it's PostgreSQL, note that you can define relationships directly in the database, and the database will automatically maintain relational integrity for you. – Robert Harvey Oct 13 '21 at 15:12
  • I use SQLALchemy. That should give independence from the data store, right? –  Oct 13 '21 at 15:26
  • Does SQLAlchemy have a way of defining first-class database relationships? – Robert Harvey Oct 13 '21 at 15:26
  • Instead of `invoice.agent_id = agent_name_id_map[agent_combo.getText()]` couldn't you write `invoice.agent_id = agent_combo.getId()`? I don't know if `getId()` is correct but imagine there's some way to get the ID of the selected item from a combo box. – Patrick McElhaney Oct 13 '21 at 22:24
  • I found that it is possible to provide the Combobox with text and some internal id. That would avoid holding and querying the maps and lets me get the id as you suggest, but, for practical effects, it is even more code lines and you need to introduce a model class "under" the combo view. For this simple case, I don't think is worth it ( Or, correct me please). I'm more interested in the database part. Is this a correct way to establish the foreign keys relationship? –  Oct 14 '21 at 07:29
  • Can you share *why* you're feel this might not be a good approach? Is there an actual relationship you're trying to model between `Agents` and `Customers`, or is this simply something that `Invoices` should keep track of? – jfaccioni Oct 14 '21 at 16:23
  • It is an actual relationship on the database I establish. It is my first big app with a database and I was wondering if this was a good practice. I can't imagine another approach. For me, it just works. –  Oct 15 '21 at 11:04

0 Answers0