[DO NOT MERGE] proposed schema

RHIT-XPrize / rhit-xprize-blocksworld

0 stars 0 forks source link

[DO NOT MERGE] proposed schema #51

Closed kgroble closed 6 years ago

kgroble commented 6 years ago

I realized before I get any farther I should get some feedback on the schema I came up with. This drops a bunch of columns that used to exist whose purpose I couldn't figure out, but I wouldn't mind adding them back if there's a good reason to.

Highlights:

Lots more tables, actual relations in our relational database
It seemed to make more sense to track times as floats offset from 0 (the start of a game) rather than timestamps, since I'm guessing any analysis would just convert them back to 0-offset floats anyway
gestures, commands, moves, and flips are all separate entities with their own tables...
... but the move_data and flip_data join tables exist so that we can bind sets of them together in AI mode and make it easy to go back and collect training data for the neural networks.

kgroble commented 6 years ago

new-schema-diagram

Diagram of proposed schema.

kgroble commented 6 years ago

Added blocks table, removed now redundant color/letter information from flip table, added coordinates to flips (to help with things like "flip the bottom block"), added redo needed flags to data tables, consolidated survey tables.

kgroble commented 6 years ago

schema-with-blocks

New diagram

Lewis-Kelley commented 6 years ago

It looks like you didn't set any of the foreign block_id's to be foreign keys.

kgroble commented 6 years ago

It doesn't seem like it gains us much. The block table should have the full cross product of game_ids and valid block_ids in it, so does a foreign key from (game_id, block_id) to the block table do anything the foreign key on game_id to the game table doesn't?

Lewis-Kelley commented 6 years ago

The functionality's the same, but the constraints are not. We can leave it as-is, since of all the data to get messed up, block_id is not one I'm really worried about.

rbubulka commented 6 years ago

I disagree block id's should be the data were most concerned about getting messed up, in the event that our block ids get borked in the table somehow it will screw up the network since then we cant get a valid set of colors and letters to pair it with

kgroble commented 6 years ago

new-schema

@rbubulka I added the constraints Lewis originally brought up, do you think there's any more we can do?