Best practice

[Daz66]

Hi all,

I'm trying to create a Quotation section in my WPF app and it's going to involve cascading ComboBoxes.

I use a local instance of MySql and ADO.NET with parameterised Stored Procedures to retrieve data.

I have created multiple category tables with FK relationships to a parent category table; my question is this:

Is it better to create a separate model/repo for each table in the application, or do I let MySql take some of the weight by creating a SP with joins and create just one SP/repo?

Is there a best practice here, or is this simply a preference issue?

I'm not interested in using ORM at this time, as a novice I can easily see when things break using my current approach.

Thanks

 

[Skydiver]

A model is not suppose to be transliteration of your database schema. A model is supposed to represent the domain object that your program is dealing with. Yes, in a lot of toy examples of the model ends up corresponding one-to-one with a table schema and database schema, and unfortunately students don't appreciate the nuance.

A repository is supposed to take care of returning domain objects from the database, or storing them into the database. It takes care of the necessary composition/decomposition, or translation from/to database schemas.

Is there a best practice here, or is this simply a preference issue?

In my opinion, yes, there is: Use an object oriented database instead of an ORM. That way you can just save the object directly into the database and not have to worry about making that object into its relational representation. Please take this with a huge grain of salt since I am in the "no SQL" camp.

 

[Daz66]

Thanks @Skydiver, I quite enjoy the sql aspect to be honest, it's something else to learn too. I'v seen references to 'no SQL' but not researched it all...might have a look....presumably LINQ involved?

The main premise was me wondering about the performance trade off between doing a bit more work server side as apposed to creating additional Models/Interfaces/repo's in the application. That said, I'm a hobby programmer and the app is never going to be huge, so performance wise, probably negligible, I was just interested.

Always value your opinion though, thanks.

 

[Skydiver]

"No SQL" is/was the movement espousing the use of non-relational databases. Originally it was just about object and document databases, but I think it's become the umbrella term for anything not relational (but not flat file).

Always defer work to the database whenever possible. Databases are written to be very efficient when storing, sorting, and manipulating data. In general queries are also very efficient unless you have bad schemas and/or bad query structures. If the database is on another machine and data has to go over the network, then even more reason to let the database use it more powerful CPUs, and just send back just the data that you need. That is more efficient than having the database send you back all the data in the database, and for you to extract what you need.

 

[Simple Samples]

Thanks @Skydiver, I quite enjoy the sql aspect to be honest, it's something else to learn too. I'v seen references to 'no SQL' but not researched it all...might have a look....presumably LINQ involved?

The main premise was me wondering about the performance trade off between doing a bit more work server side as apposed to creating additional Models/Interfaces/repo's in the application. That said, I'm a hobby programmer and the app is never going to be huge, so performance wise, probably negligible, I was just interested.

Always value your opinion though, thanks.

I am not a database expert but I know there is an abundance of developers that use SQL databases. As far as I know, your requirements can be easily satisfied by SQL databases.

I think that the main performance advantage of a stored procedure is when there is a network between the server and the application. A stored procedure can reduce the amount of data to be sent to the application.

As for LInQ (for SQL databases), it is often converted to SQL.