mercredi 21 avril 2021

How to design model in MVC when database tables are linked and I need to query both? (Nodejs, PostgreSQL)

I'm currently having trouble wrapping my head around the MVC design pattern and how to structure my models. As far as I understand, the model file in MVC would be how one interacts with the database and retrieves information from it, where one model file would be equivalent to one database table, i.e. a User table in my PostgreSQL database would correlate with a user.js file in my project/model/ folder where the user.js would contain the class associated with the model and it's methods such as findAll, findById, etc functions.

Say for instance I have two tables: customer and purchase, such that:

customer:

| customer_id (PRIMARY KEY) | customer_name | customer_email |
| --------------------------| --------------| ---------------|
| 1                         | John          | john@j         |
| 2                         | Ben           | ben@b          |

purchase:

| purchase_id (PRIMARY KEY) | purchase_item     | purchase_outstanding | purchase_customer_id (FOREIGN KEY) |
| ------------------------- | ------------------| -------------------- | ---------------------------------- |
| 10                        | Mercedes S-Class  | 999                  | 2 |
| 11                        | Volkswagen Golf   | 21398                | 1 |
| 12                        | Volkswagen Beetle | 9812                 | 1 |
| 13                        | Ford Fiesta       | 3921                 | 2 |
| 14                        | Ford Ranger       | 1000                 | 2 |

And my query:

SELECT purchase.purchase_id, purchase.purchase_oustanding, customer.customer_name
FROM purchase
INNER JOIN customer
ON purchase.purchase_customer_id = customer.customer_id
ORDER BY purchase.purchase_outstanding DESC;

My questions:

  1. Almost all examples of MVC I've seen employ methods that only query one table, such as a findAll() function in the purchase.js file which queries for all rows in the purchase table. Is it bad design for a model to have a method that's able to query other tables other than it's own such as the above query?
  2. Is it poor design to create a single method/function in my purchase.js file such as findOutstandingDesc() SOLELY for the above query? Or would it be better to chain methods defined in other models?
  3. Do I necessarily have to define my table schema as classes in the model file (such as having a Purchase class with attributes like purchase_id, purchase_item, etc), or can I just create a bunch of functions inside the file and export them from it?

Thank you!

Aucun commentaire:

Enregistrer un commentaire