Database Design Question

Question

I am creating a basic CRUD Address Book application using a SQL Database and C# application. I am looking for design assistance to see if I am designing the database correctly and coding it in a “best practice”. I plan on using this application as the foundation of a larger application going forward, thus I need this to be designed well and scalable for large numbers of records and users accessing the application.

I attached a document with the database design diagram and c# code. I really appreciate you taking the time to give me your feedback. 57445-databasedesignquestions.pdf

Answer 1

The link may help you. It's not about azure per say.

https://learn.microsoft.com/en-us/dotnet/architecture/modern-web-apps-azure/architectural-principles

About Transactions and data persistence, it's all records in a transaction scope are persistence to the database, or all records are rolled back if one record in the transaction scope is not persisted to the database, which prevents orphaned records being persisted. EF does transactions in data persistence automatically where warranted.

I don't think the code you have presented is optimal for scalability or speed for the following reasons:

1) Async is not being used.
2) Dependency injection is not being used, like on the connectionstring
3) Interface is not being used implementing loose coupling.
4) A persistence design pattern is not being used, like the generic repository pattern with Dapper or the Data Access Object Pattern.
5) No Service Layer is being used making the calls to DAL objects for CRUD operations used by the client.

Yes, I also start with the backend, the DAL before I come to the BLL and PL. The Service Layer and DAL should not care what layers and objects above them use the SL and DAL or what type of project uses them Desktop or Web.

https://exceptionnotfound.net/using-dapper-asynchronously-in-asp-net-core-2-1/

The link on Dapper is using the generic repository pattern. There is nothing wrong with the repository, a domain pattern, when used properly in the domain. Repository pattern is not a data persistence pattern. That's why I use the DAO pattern for CRUD.

You can use Core in ASP.NET and Windows Desktop solutions, and it should be the coding path you are moving towards.

It does not matter that you are using ASP.NET Core or Windows Desktop, becuase writing clean code applies to both, even for non Core solutions

https://learn.microsoft.com/en-us/archive/msdn-magazine/2016/may/asp-net-writing-clean-code-in-asp-net-core-with-dependency-injection

https://ardalis.com/new-is-glue/

Example solution on GitHub.

Proper usage of DTO pattern is being shown. All DTO(s) are in the Entities project, and all projects that need to know about the DTO(s) have reference to Entities.

https://github.com/darnold924/PublishingCompany

The persistence model objects used by Dapper don't travel. The DTO(s) travel.

Answer 2

@Steven Rieger

Being a bit of a control freak, I like using Dapper and controlling all aspects of the code.

I don't see where Dapper gives one controlling all aspects of CRUD operations with the database. EF can certainly do the same if one knows how to effectively use EF or any ORM for that matter.

You should consider using SoC and using a DAL.

https://en.wikipedia.org/wiki/Separation_of_concerns

Look into possibly using layered or n-tier.

https://learn.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

Look into possibly using the DAO pattern in the DAL.

https://javarevisited.blogspot.com/2013/01/data-access-object-dao-design-pattern-java-tutorial-example.html

The database is just one aspect of it. What about the UI?

Look into to using the DTO pattern.

https://www.codeproject.com/articles/1050468/data-transfer-object-design-pattern-in-csharp

Answer 3

@Duane Arnold , Thank you for the links. I'm familiar with most of what you provided. I believe I am using an N-Tier / DTO approach as I'm hoping you can see in the attached PDF.

I am working on the backend only right now. Once finished I will build an API to access the backend and finally a Web/Mobile UI to consume the API. Since this is my first attempt at a "full-stack" application, I am hoping to gain what the best practices are so the entire project will perform and scale well.