How would you design a database for storing mazes and games played on the same mazes?

I have an assignment to design a database for storing 开发者_JAVA百科mazes and games played on these mazes.

A maze is essentially a grid, containing prizes and traps. The assignment isn't more specific: it asks us to find some kind of general rules to express events that occur in game and rules for these events.

I've put down a simple conceptual schema of it:

Do you think that, based on my description of the assignment, this is a good solution? How can I improve it?

So a maze is a grid, with each node containing data. Each node contains several things: 1) a list of links to other nodes 2) a list of items available at that node

Everything else can be expressed as those.

That suggests a table MAZE, containing basic data about the maze like its ID, NAME, DESCRIPTION, etc. Then there's a table NODE, containing data about the node. ID, NAME, COORDINATES, and MAZE_ID 3rd ITEM, with ID, ITEM_NAME, NODE_ID, ITEM_TYPE, and maybe other things.

To speed up lookup operations, you might want a link table MAZE_NODE containing just MAZE_ID and NODE_ID with foreign keys to both tables, and a NODE_ITEM table with similarly NODE_ID and ITEM_ID fields.

Other columns and tables may be needed to hold information not specific to the data hierarchy (like information about the stats of items, environment conditions at nodes, etc.).

And that's just a very basic data structure for the maze. For the lore story, you'd at the very least need tables with texts, chapters, conditions under which each chapter is launched, etc. etc. This is pretty much independent of the maze, though the conditions might include the player being in a specific location in the maze, or telling him to go to a specific location.

You should separate the tasks into

1) store a maze 2) store events

Considering (2) you are fine with a relational database logging events even including movement of your hero in a single table listing the columns:

game | time | actor | eventtype | eventdata

(Do not create a table per game ;)

Considering (1): You should store the maze as a grid. This can be implemented by an array of e.g. single bytes (capable of storing wall, free, plus 254 items) per grid-cell describing its type. It is up to you where you store this array but if you store it in a database do not try to split maze and cells into different tables (the 1:n relationship you described), just store the maze as a BLOB in table which contains the BLOBs of all your mazes.