MySQL Forums
Forum List  »  Newbie

Re: Assistance with database structure
Posted by: Phillip Ward
Date: January 25, 2016 07:04AM

Members table - one row per Member.

Games table - one row per Game, including references to two Member records.

You don't need a Scores table - given your description, a Score references a Game, so there's one Score for every Game, so the two can be squished together into the same table.

That's it.

Your "three-dimensional" aspect is handled by this structure, which effectively holds a sparse array linking any Member to any other Member.

Table: Members 
id: PK 
. . . 

Table: Games 
id: PK 
member_1_id 
member_2_id
score
date_played 
. . .

To prevent unnecessary duplication, ensure that your application stores the two Member identifiers consistently (e.g. lower then higher identifier values).

+-------------+-------------+ 
| member_1_id | member_2_id | 
+-------------+-------------+ 
|          77 |          88 | 
|          88 |          77 | <-- avoid this 
+-------------+-------------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Assistance with database structure
January 25, 2016 07:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.