I want to model, analyze and create a database consisting of information from a card game. Each round of the game would have an ID, time stamp, and 4 or more players (usually 4). I have defined my player table with its set of attributes. However since each round has 4 players, how do I efficiently implement the relationship between the round and the players?
Essentially each round has 4 distinct players, with the same attributes (with different values). It would seem odd to have one attribute(column) for each player since I would like to have some kind of link to a player table to keep my 'objects' independent and encapsulated. Is this a one to many relationship between the round and the 4 players?
When I draw my model on paper it seems to be more like a hierarchy. For example, I have a Round that has 4 Players and each Player has a Hand of cards. Is this is a relational model, hierarchial model, object model, or something else?
Also, can this be done in SQL? If not what should I use? Any help/suggestions are much appreciated since all my experience is with relational databases in SQL.
Thanks!
Basically
Round Player
------------ -----------------
round_id player_id others_attri player_id info_attributes
Then, for each round, you need storage 4 rows in Round, with the same id and each player id, example, Round with id 1 where players 1,2,3,4 are in.
Round Player
----------- -----------
1 1 date 1
1 2 date 2
1 3 date 3
1 4 date 4
player_id is a foraign key to player_id.
Or if you dont want storage date many times, do that:
Round player_play Player
--------- ------------ -----------------
round_id info_attributes round_id player_id player_id info_attributes
Round player_play Player
------- ----------- -----------
1 date 1 1 1
1 2 2
1 3 3
1 4 4