I am busy with a score keeping app. And would appreciate suggestions/ help. I will try to be brief and hopefully have explained the main gist of the app below

The app basically maintains some scores. The way they are set-up is using scoresheets. Basically a scoresheet contain the name , the count (no of occurences), and if the score is captured twice (repeats). The scoresheet is used to create a scorecard. Which will be used to create a score table.

Table structure below :

  create table #scoresheet(
    id int identity,
    scoreSheetName nvarchar(30),
    scoreCount int ,
    canRepeat bit

This scoresheet table is used to create/ maintain a scorecard table which will contain the scoreSheetId and generate the name of the score

Table structure below :

   create table #scorecard(
     id int identity,
     scoreSheetId int,
     scoreName nvarchar(30)

   create table #score(
    id int identity,
    scoreCardId int,
    score int,
   comment nvarchar(150),
   isApproved bit

Example Data:

   insert into #scoresheet values ('Golf', 2, 1)
   insert into #scoresheet values ('Soccer', 1, 0)

   insert into #scorecard values (1, 'Golf1') --count is two so two entries 
   insert into #scorecard values (1, 'Golf2') --count is two so two entries 
   insert into #scorecard values (1, 'Golf1Repeat') --repeat set to true
   insert into #scorecard values (1, 'Golf2Repeat') --repeat set to true

   insert into #score values (1, 60, 'Great Score', 1)
   insert into #score values (2, 90, 'Approved', 1)

So for example a scoresheet with 'Golf", occurences 2 , repeat 1(true) will generate a scorecard with 'Golf1', 'Golf2' , 'Golf1Repeat', 'Golf2Repeat'. The issue I am dealing with is that the scoreSheet can be edited, i.e the number/count/scoreCount and whether it repeats can be changed and I need to maintain the scoresheet table either adding or deleting items.

Ultimately the scoresheet table is used to populate the score table but I have that done.

Basically what I have done so far is to populate the scoreCard table using the scoresheet table. If the count is 2 I insert the value twice with the name of the scoresheet + index e.g. Golf1, Golf2. If it repeats insert Golf1Repeat, Golf2Repeat. If the count was reduced I delete extra entries. If the count was increased I increase/ insert more scoreCards

My question is this the best way to do the ScoreSheet and ScoreCard table. Is there a better way to do this? Can re-design tables if there is a better way

  • Your business rules are not clear to me at all! Try to describe it in terms of business_event leads to transaction which has input and output (forget how the tables are set for now)...Maybe this way you can get more help.
    – NoChance
    Mar 8 '19 at 14:29
  • Thanks @NoChance. We need to capture scores for a given sport eg Golf, the number of times (scorecount) we capture the score is not specified beforehand and can change upwards or downwards at any time. So we can have Golf1, Golf2, Golf3 etc and capture score against them. If we the scorecount increases we have golf Golf4 etc, if it reduces we delete the extra scores. That's the main gist please let me know if it helps.
    – alphacoder
    Mar 8 '19 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.