These are the considerations during the various stages of database setup

Database project

Using a visual studio SQL server database project allows the group to share a common schema and setup of the database, and to have a variant of the database running on their local machine. It also gives increased control over the testing, as the existence of a post deployment script allows for easy setup of the same test multiple times in a row.

Tables

Should there be a Guest and a Reservation table in the database? Should we have 3 tables maybe? Reservation Information, Guest Information and a combined table of these? Or a Reservation State table? How should archiving/deleting be handled in any case?

it might prove worhtwhile to produce something like a 'calendar' table, which would simply reference the reservations. It might make for a quicker query for the reserved dates. Then again, it might compromise our object models, so I am uncertain.

Reservation

For dates in sql, there exists a SqlDataReader.GetDatetime method

While describing the database table based on the report models, it occured to me: should 'approved', 'deposit paid', 'rent paid', 'deposit refunded' and 'cancelled' be represented with dates instead of boolean values (bits in SQL)? In this case, they should allow null.

Guest

The primary reason to have users in a separate table is for future extension with user login information. Even then, further extension of the database may be required.

Should there be a 'type' in the database model, to handle the fact that users are generally abstract, and there are different types.

Administrators

Should the members of the association be represented strongly in the database, and perhaps keep track of the 'transactions' they perform on reservations?

Stored Procedures

So far as is possible, keep the Sql queries as parameterized, stored procedures. This will keep complexity low and extension easy. I have aimed for coverage of all functionality as per the functions and requirements specification. Thus, both current tables have CRUD functions (Create, Read, Update, Delete).

Reservation

I've started in the reservation table, where I've created two getters, one for all reservations, and one for a single reservation based on an Id. We might want to have one to get for a reservation based on a date too.

The create(Insert) procedure makes a reservation with a start date, end date and a guest id, so the guest has to exist in the database before the reservation. This is one of the problems with a declarative database such as Sql, when used for an OOP project.

Update was tricky to make extensible, as optional parameters are a bit verbose. I believe the current solution works though. It sets each parameter to NULL by default, and then tests with ISNULL. For the Nullable values, this means they can't be reset without creating a new table entry, but since those updates are based on events it should be alright.

Delete simply takes and Id and removes that from the table.

Guest

The Guest table has similar stored procedures, except for update. Name can't be updated in the guest table with the stored procedure, since stored procedures should be for the normal things that can change. If a name change is required, this is a very special case, and should be handled that way.

Post Deployment Script

I've made a simple post deployment script which adds four guests and four reservations, one for each guest. The dates are based on the current date when the database is published. No solution found for 'refreshing' the data in the database yet, in case we wish to work from scratch. Maybe it works to simply publish again?

How to start the database

I should add a guide to the readme on how to create a local database from the Visual Studio project.

Data Access

This is a second project to handle the connection to the database. This project uses three NuGet packages: Dapper (micro ORM), Microsoft.Extensions.Configuration (handles configurations) and System.Data.SqlClient (handles the SQL connection).