Attendance_Add_tr.sql

create procedure dbo.Attendance_Add_tr
  @OrganisationId int,
  @PersonId int,
  @AttendanceDate date
as

set nocount on;

------------------------------------------------------------
-- Validation block
------------------------------------------------------------
set transaction isolation level read committed;

if not exists (
  select 1
  from dbo.Person
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
)
begin
  return 8; -- Person does not exist
end

if exists (
  select 1
  from dbo.Attendance
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and AttendanceDate = @AttendanceDate
)
begin
  return 7; -- Attendance on the given date has already been recorded.
end

------------------------------------------------------------
-- Execute block
------------------------------------------------------------
set transaction isolation level serializable;

begin transaction;

insert into dbo.Attendance (OrganisationId, PersonId, AttendanceDate)
select @OrganisationId, @PersonId, @AttendanceDate
where not exists (
  select 1
  from dbo.Attendance
  with (updlock)
  where OrganisationId = @OrganisationId
    and PersonId = @PersonId
    and AttendanceDate = @AttendanceDate
);

if @@ROWCOUNT <> 0
begin
  commit;
end
else
begin
  rollback;
end;

return 0;

Attendance_Get_tr.sql

create procedure dbo.Attendance_Get_tr
  @OrganisationId int,
  @PersonId int,
  @AttendanceDate date
as

set nocount on;
set transaction isolation level read committed;

select OrganisationId, PersonId, AttendanceDate
from dbo.Attendance
where OrganisationId = @OrganisationId
  and PersonId = @PersonId
  and AttendanceDate = @AttendanceDate;

return 0;

TableDDL.sql

create table dbo.Organisation (
  OrganisationId int not null,
  OrganisationName nvarchar(100) not null,
  CreatedDtm datetimeoffset (0) not null,
  UpdatedDtm datetimeoffset (0) not null,
  
  constraint UC_Organisation_PK
  primary key clustered (OrganisationId)
);

create table dbo.Person (
  OrganisationId  int not null,
  PersonId int not null,
  GivenNames nvarchar(50) not null,
  FamilyName nvarchar(50) not null,
  CreatedDtm datetimeoffset(0) not null,
  UpdatedDtm datetimeoffset(0) not null,
  
  constraint UC_Person_PK
  primary key clustered (OrganisationId, PersonId),
  
  constraint Organisation_Hosts_Person_FK
  foreign key (OrganisationId)
  references dbo.Organisation (OrganisationId)
);

create table dbo.Attendance (
  OrganisationId int not null,
  PersonId int not null,
  AttendanceDate date not null,

  constraint UC_Attendance_PK
  primary key clustered (OrganisationId, PersonId, AttendanceDate),

  constraint Person_Indicates_Attendance_FK
  foreign key (OrganisationId, PersonId)
  references dbo.Person (OrganisationId, PersonId)
);

Done.

Dan

I am in.

Where did the comments [removed from here] go ? I have the next submission ready, but it is meaningless without the thread of previous comments.

Just looking around (I am new to GitHub).

My expectation is this:

1. this thread is public, to allow Nicola, for the purpose of discussing the OLTP/Transaction/ACID/SQL/Template (only)
2. the ShootingClubDatabase is a repository, where we (just you and me) will two things
   • maintain a code repository & folder for DMs - I can see all of this
   • develop a data model - where is this ?

That latter is where I expect the most back-and-forth.

If I want to comment on (eg) a particular code item, where do I do that ?

Cheers
Derek

Hi Derek,

All of the content moved here: DanielLoth/ShootingClubDatabase#2 (the pull request is titled 'V1 diagram')

At present the repository is actually public, so others can see it.
I'm happy to make it private if you'd prefer the repository itself to be private.

A second pull request with a refreshed data model is here: DanielLoth/ShootingClubDatabase#4

---

For commenting on code itself, such as SQL code, this pull request can be used: DanielLoth/ShootingClubDatabase#3
You can click the 'Files changed' tab, which will show all files, and then click a line of code (or drag-select multiple lines) and leave a comment.

Cheers,
Dan