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 )
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