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

Looks good for me. If the data would grow very large, I suggest planning table partitioning based on region or zip code or state or country for easy maintenance in future.

I would also recommend using a big data solution like mongodb on Azure cosmos db to be schema independent. Future schema changes on large tables on any relational database is tedious.

Answer 3

Hi StevenRieger-9681,

scalable for large numbers of records and users accessing the application.

When you need SQL Server to handle more load, there’s two ways to do it:

• Scale up – using a SQL Server with more CPU power, more memory, or more storage throughput.
• Scale out – accommodates variable workloads by hosting data across multiple databases.

Please refer to Scale Up or Scale Out? Scaling SQL Server which might help.

Best Regards,
Amelia

Answer 4

Hello,

The model seems good, have you considered using Entity Framework Core (5 is the current stable release) as this would mean you don't have to code for base CRUD operations. Entity Framework Core works great with Windows Forms, WPF and web based projects e.g. ASP.NET Core. When using EF Core things like your insert statement can be done in one or two lines and an insert can do multiple inserts e.g. populate a Person with one or more addresses and done.

Creation can be done with a free scaffolding tool that also permits migrations known as EF Core Power Tools that works within Visual Studio.

Answer 5

I have looked at EF and found it to be a bit of a black box. Also, from various training videos on EF vs Dapper, Dapper seems to be the better alternative. I can appreciate the EF tooling to make mundane tasks quicker and easier though.

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

Thank you for the feedback.