How would you design the database mannequin by a given area mannequin? – Code Evaluate

I want to enhance my Go expertise and considered making a small Minesweeper backend. For the area mannequin I began with this

import "github.com/google/uuid"

kind Sport struct {
	ID            uuid.UUID
	Board         [][]*BoardCell
	AmountOfMines uint8
}

kind BoardCell struct {
	IsRevealed                    bool
	HasBeenRevealedAfterEndOfGame bool
	HasFlag                       bool
	HasMine                       bool
	AmountOfNeighbourMines        uint8
}

With these info you may examine the entire sport board whereas monitoring the AmountOfNeighbourMines discipline is only for efficiency functions ( since we solely must calculate it as soon as )

I want to understand how you’ll design a database mannequin for this.

I personally suppose that I might use the identical schema as SQL tables
Sport => ID, AmountOfMines
BoardCell => gameID ( overseas key ), …fields…

however with regards to a DTO I’m undecided if I ought to merely copy paste the area mannequin. If that’s the case the in reminiscence database might observe video games in a map [gameID, game] however perhaps there are higher methods to handle the video games within the database layer.

Thanks upfront

Is it typical for posts right here to go unanswered? Making an attempt to know if the discussion board remains to be energetic or not.

I’m right here.

Concerning your query, I’m not completely certain I perceive all the pieces you’re asking. Nevertheless, I strategy design from a database perspective.

I perceive that you just want the AmountOfNeighbourMines to save lots of processing energy in some unspecified time in the future, however I favor to not embody it within the database. I assume you need to put it aside at a selected level, appropriate? So efficiency doesn’t matter that a lot and all information is there.

You haven’t supplied a lot info, however I really feel that individuals typically use an excessive amount of object-oriented programming (OOP) for Go. Transferring a Information Switch Object (DTO) feels unnecessarily complicated. It could be higher to easily ship the required knowledge instantly and suppose in these phrases.

Once more, it’s arduous to make certain with out extra particulars, however my instinct tells me that your code may need at the very least one or two pointless layers of complexity and is extra in a Java or Cpp fashion.

I don’t suppose I might retailer issues like AmountOfMines in my sport desk since it may be calculated. Perhaps begin with one thing like this (BTW I typed this into notepad so syntax could be barely mistaken and I’m presently used to MariaDB so that is MySQL-ish):

-- Create video games desk
CREATE TABLE video games (
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);
-- Create sport cells desk
CREATE TABLE game_cells (
  id int(11) NOT NULL AUTO_INCREMENT,
  game_id int(11) NOT NULL,
  x int(11) NOT NULL,
  y int(11) NOT NULL,
  is_revealed tinyint(1) NOT NULL,
  is_flagged tinyint(1) NOT NULL,
  is_mine tinyint(1) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (game_id) REFERENCES video games(id)
);

Anyway, my level there may be: you might simply assemble issues like AmountOfMines:

choose 
	rely(id) as num_cells,
    sum(is_mine) as num_mines
from game_cells the place game_id = 1;