database - Joining pivot table vs. duplicating columns -
i'm making game, players make moves 1 one. have following tables:
╔═════════╗ ║ players ║ ╠═════════╣ ║ id ║ ║ name ║ ╚═════════╝ ╔════════════╗ ║ games ║ ╠════════════╣ ║ id ║ ║ started_at ║ ╚════════════╝ ╔═════════════╗ ║ game_player ║ ╠═════════════╣ ║ id ║ ║ game_id ║ ║ player_id ║ ║ turn ║ ╚═════════════╝ now, i'd add moves table keep track of game's history. i'm not sure way better:
1) connect moves pivot table game_player this:
╔════════════════╗ ║ moves ║ ╠════════════════╣ ║ id ║ ║ game_player_id ║ ║ made_at ║ ╚════════════════╝ 2) duplicate game_id , player_id pair this:
╔═══════════╗ ║ moves ║ ╠═══════════╣ ║ id ║ ║ game_id ║ ║ player_id ║ ║ made_at ║ ╚═══════════╝ the first solution makes data more concise, there no way put unexisting game-player pair if use foreign keys.
the second solution easier handle orm.
how should problem solved? there way use first idea comfortably orm, perhaps creating entity game-player pairs, participation? how guys it?
initial response
your files not have uniqueness or integrity. understand it, want relational database, relational integrity, power, , speed.
here data model, discussion point. not complete, many details need raised , closed. however, getting somthing out there faster question-and-answer back-and-forth, using media.
fist draft game data model
please comment/discuss.
if not used notation, please advised every little tick, notch, , mark, solid vs dashed lines, square vs round corners, means specific. refer idef1x notation.
response comments
it seems far complicated goal
which goal ?
if goal relational database (as part of whatever other goals have project), minimum "complexity", configuration required produce relational database, relational integrity. onus on climb learning curve.
otherwise, please remove database , relational-database tags question.
how first idea against database integrity?
it isn't against integrity, absent integrity, kind normal, ordinary, expected, in relational database. clearly, have been reading books allege "relational databases", written people evidently clueless relational databases, , have implemented advise, or have seen others do.
you have no row uniqueness demanded relational model. eg. duplicate player.names allowed. eg there no context game.
you have
idfields in every file. gives record id (in rfs) uniqueness, no row uniqueness. please read this answer carefully.eg. in both (1) , (2), allow moves players not constrained game playing. existing player_id , game_id allowed.
the above example of type of integrity errors have, have not listed them all.
my suggested model fixes integrity errors, did not enumerate errors fixed, gave valid database.
getting somthing out there faster question-and-answer back-and-forth
eg:
the notion of game exists independently not reasonable. game exists in context of player started one. have implemented that. improves identifiers , game more contextual.
likewise, notion of move exist without game, or without players playing in specific game, not reasonable. yo have that. model fixes that.
first have responders join game, 1 player started. sum of them players specific game.
next, have moves constrained each specific game.
next, have moves constrained each specific palyer in each specific game.
next, in order implement correctly, use basic, relational, basetype::subtype structure. differentiates initiating_player , responding_players. allows me apply relevant constraints correct tables.
is_initiatorcolumn discriminates basetype.- if more information on subtypes, please study this document.
all 1
idcolumns have disappeared, naturally, without trying rid of them. 1 remaining player_id valid because player.name might wide. if player.name not real name, if user_name (eg.char(12)), change player_id user_name, , whole structure better. please advise, , improve model.
now have basis relational database. did label first draft, expected questions , perhaps minor changes. did not expect cover large gap between record filing systems , relational databases. feel free ask questions, might suggest opening new question require detailed answer.
Comments
Post a Comment