Am I Setting Up My Database Correctly? Or Am I Thinking About Things The Wrong Way?

Question

So I'm having a little self-doubt as to whether or not I am setting up this database I am working on correctly or not. This is the first large-scale database project I have ever worked on, and I'm doing I've been tasked with doing it alone. So you know, no pressure at alllllllll.

This question isn't about whether or not I am setting up my tables correctly. I at least have enough confidence in myself to say I know how to create proper relational database tables, and how to normalize them properly.

What I am concerned with are in facts things like Views, and Stored Procedures.

Quick synopsis of what the project is. I am retrofitting the company database, updating it to a new relational model. I need to set up an entirely new database structure from scratch for all the data, and then alongside the database create a VB.Net program that employees can use to interact with the database in various ways.

You see I've basically set up the database so that the Tables act as where all the data is stored, and the Views act as where all the data is retrieved. I figured that setting it up this way makes it so that it is easier to select data that needs to be shown to people using the .Net app since Views already contain all the proper Joins for all the data. Sure if you need to select just one piece of data from a table, you can do a simple table query, but for large data sets I figured Views were the way to go. Was I correct in thinking this?

And then there are the Stored Procedures. Essentially if the VB.Net project is doing anything variable related for data collecting, I am just setting up a stored procedure within the database to take those variables and work with internal SQL script.

For example, let's say I need to select everything from my PartsData View that has a specific supplier. Instead of creating a large query string in VB.Net and then passing that String as an SQL Command, I am instead just passing the supplier name as a variable to a 'PartSupplier' Procedure on the database which spits back the information I need.

To me this feels like it just makes more sense to do it this way, as opposed to just constructing long String queries in VB.Net and then throwing them at the database. Part of what isn't helping my confidence in my approach is that my boss seems to think I'm wasting time and being stupid for doing things my way. :\

I know this isn't exactly something anyone can give me a 100% answer on because I know that you can't give me something like that without knowing all the information. But am I on the right track here? Are there some obvious flaws with my logic you can easily see?

Answer 1

Views: Your approach is a very widely-accepted practice. The benefits are well-documented: https://www.c-sharpcorner.com/blogs/advantages-and-disadvantages-of-views-in-sql-server1

Stored Procedures: Also, as you described it, widely-practiced (the disadvantages are notable, too) http://sqltechtips.blogspot.com/2015/11/stored-procedure-benifits.html

In my experience it's common to see views and stored procedures used for the reasons stated, and they behave as advertised.

Whatever method you choose, I strongly advise consistency, as it can be frustrating to have to look in many places for business-rule implementation: code with embedded SQL, stored procedures, views, ORM... try to apply some systematized implementation to limit the number of places you have to look for constraints, for example - it's possible to put them in any/all of the places I just listed.

Answer 2

I don't think there is a right answer to this design question. It all depends on your situation.

Here is a nice link I found explaining the pros and cons of using stored procedures for data access. It also presents alternatives such as in-line/parameterized queries and ORM's (Hibernate & NHibernate)

https://www.seguetech.com/advantages-and-drawbacks-of-using-stored-procedures-for-processing-data/