Table Design (2 Viewers)

[ridonc]

Im making a database for a competitive game, having trouble getting the proper tables/relationship. The game is Warships where 7 people from our clan compete against other clans and their 7 players. Each game, their is a different map that is played on and players use many different ships. We are trying to track Win/Loss for each of our players, win/loss for each ship, and the win/loss on each map. Every match, the players change as well as the ships they use. I obviously have the following tables but am having trouble with the many to many tables to allow the adding of 7 players and the ship they are playing:
-Ships Table
-MapName Table
-PlayersTable
-MatchTable
-MatchData Table (the many to many table)
-Ships Table

Any help is appreciated

 

[plog]

Overall it sounds like you need more than one many to many tables. I don't fully understand the real world system you are modeling but I can help none the less

My advice is to open Excel and sketch out your tables/fields. In A1 put Ships in bold and then in A2 put the first field that goes in Ships and in B2 put its datatype(Date, number, text), in C2 put a note explaining what that field holds. Then in A3, B3, C3 put the next field in Ships, etc, etc.. When done with all the Ships fields, put your next table in the next empty A column cell, bold it and then lists its fields. Do this until all tables/fields are used. If you have any fields you need but don't know where they go make an extra area and put them there.

Review all your fields and make sure they all seem right. When they do, open up a blank instance of Access and create all those tables/fields. Once that is done, open up the RElationship Tool in Access and add tables 1 by 1. STart with Matches and then add what you think is the next logical table to connect to it and set up a relationship between them. Do this 1 by 1 for until every table is in there. But, you can only connect a new table to 1 existing table in the Relationship Tool, if you want to connect a new table to more than 1 existing table in the Tool then you are wrong somehow and must figure out how to make that new table only connect to just 1 existing table. That step is where you will most likely identify the need for those additional many to many tables.

Give that a shot, then take a screenshot of your Relationship Tool and post it back here so we can ask question of it to make sure it is right for your data.

 

[ridonc]

Overall it sounds like you need more than one many to many tables. I don't fully understand the real world system you are modeling but I can help none the less

My advice is to open Excel and sketch out your tables/fields. In A1 put Ships in bold and then in A2 put the first field that goes in Ships and in B2 put its datatype(Date, number, text), in C2 put a note explaining what that field holds. Then in A3, B3, C3 put the next field in Ships, etc, etc.. When done with all the Ships fields, put your next table in the next empty A column cell, bold it and then lists its fields. Do this until all tables/fields are used. If you have any fields you need but don't know where they go make an extra area and put them there.

Review all your fields and make sure they all seem right. When they do, open up a blank instance of Access and create all those tables/fields. Once that is done, open up the RElationship Tool in Access and add tables 1 by 1. STart with Matches and then add what you think is the next logical table to connect to it and set up a relationship between them. Do this 1 by 1 for until every table is in there. But, you can only connect a new table to 1 existing table in the Relationship Tool, if you want to connect a new table to more than 1 existing table in the Tool then you are wrong somehow and must figure out how to make that new table only connect to just 1 existing table. That step is where you will most likely identify the need for those additional many to many tables.

Give that a shot, then take a screenshot of your Relationship Tool and post it back here so we can ask question of it to make sure it is right for your data.

Thanks so much for your help, here is what I have so far. Thing Im struggling with is how to have more than 1 player per Match (or you could say per "game") and to attach each player to the ship they played during that game as the goal of this DB is to track player success but also their success in different ships. Their are 7 players per game, who can all be in different ships. We are not interested in tracking the opposing team or their ships.

Thanks again. Hope im on the right track

 

[ridonc]

Overall it sounds like you need more than one many to many tables. I don't fully understand the real world system you are modeling but I can help none the less

My advice is to open Excel and sketch out your tables/fields. In A1 put Ships in bold and then in A2 put the first field that goes in Ships and in B2 put its datatype(Date, number, text), in C2 put a note explaining what that field holds. Then in A3, B3, C3 put the next field in Ships, etc, etc.. When done with all the Ships fields, put your next table in the next empty A column cell, bold it and then lists its fields. Do this until all tables/fields are used. If you have any fields you need but don't know where they go make an extra area and put them there.

Review all your fields and make sure they all seem right. When they do, open up a blank instance of Access and create all those tables/fields. Once that is done, open up the RElationship Tool in Access and add tables 1 by 1. STart with Matches and then add what you think is the next logical table to connect to it and set up a relationship between them. Do this 1 by 1 for until every table is in there. But, you can only connect a new table to 1 existing table in the Relationship Tool, if you want to connect a new table to more than 1 existing table in the Tool then you are wrong somehow and must figure out how to make that new table only connect to just 1 existing table. That step is where you will most likely identify the need for those additional many to many tables.

Give that a shot, then take a screenshot of your Relationship Tool and post it back here so we can ask question of it to make sure it is right for your data.

Here is how my form wizard came out, is this on the right track too?

 

[plog]

Forms are a ways off, so don't even think about those yet. You need to get your tables and fields correct.

Also, keep the terminology as simple and concise as possible for us. We don't know your real world system for this, so no synonyms. Every term you use we think is its own unique concept. Your initial post talked about matches and this new screenshot has games. They may mean the same in this arena, but if we are talking about tennis I know they are distinct concepts. Same with "team" and "clan". I think they are the same but aren't certain. Pick one term and only use it for a concept--preferably the one you are using for table names.


As for the screenshot of relationships:

You should not be able to draw 2 different paths from Players to Games. I don't know which path is correct but at least 1 of them is wrong. My guess is GameData isn't in the right place either (or named correctly). This is why you should build your relationship tool one table at a time.

Can a player be in more than 1 clan?

Does a player have certain ships available to them or all ships available to them? Ships A, B, C...X, Y, Z exist, but is it possible that Jim can only use ships G, M and R? Or can Jim choose any of the 26 ships?

 

[The_Doc_Man]

First comment after looking at the diagram is to consider that you have SIX tables with a field called ID. You admit to being new so here is a bit of naming advice. NEVER do this kind of chaos to yourself.

The "ID" field probably needs to exist for most of those tables because a primary key candidate field will be needed for any defined relationships. However, you want those IDs to have a project-wide unique name like GameID, ShipID, PersID, etc. This is because if you ever have to do a JOIN query (and with many-to-many junction tables, that's about 99.99% likely), having ID in more than one contributing table means confusion and trouble. Fix this in the design phase and it will be an easy fix. Fix that much later down the road and you will have a lot more editing to do.

Second comment: From your description, you have a Game which will use a given map, seven players, and some number of ships. So you have a game with a (probably autonumber) Game ID and you allow a many-to-one relatinship of players. Does each player have ONE ship (only) or can they command multiple ships? Because that might make a huge structural difference. If the answer is NO, then no problem, but I have to ask because my grandson and I play various simulation games that have a similar concept but probably on a smaller scale.

Third comment: You have to be careful with Clans. In your diagram you have an absolute no-no - between game data and players you have TWO paths that go through two different tables to end up at the same ending table. This ambiguity of pathways leads Access to become easily confused and in fact to refuse to use some of its internal "smarts" to help you design things. It can also lead to situations where a query that involves multiple tables that reference both persons AND clans to give you what we call a "permutation" JOIN which leads to multiple paths, making any query non-updateable due to an Access "uniqueness rule" that applies to update queries. (And to SELECT queries through which you might wish to make a manual update in the query's datasheet view.) My thought here is that your Clan history needs to be solely derived from the clans of the players. I.e. there is no place for clans in the game until you have players in the game.

EDIT: I see by the time tags that plog beat me to some of those questions by about 5 minutes.

 

[ridonc]

Forms are a ways off, so don't even think about those yet. You need to get your tables and fields correct.

Also, keep the terminology as simple and concise as possible for us. We don't know your real world system for this, so no synonyms. Every term you use we think is its own unique concept. Your initial post talked about matches and this new screenshot has games. They may mean the same in this arena, but if we are talking about tennis I know they are distinct concepts. Same with "team" and "clan". I think they are the same but aren't certain. Pick one term and only use it for a concept--preferably the one you are using for table names.


As for the screenshot of relationships:

You should not be able to draw 2 different paths from Players to Games. I don't know which path is correct but at least 1 of them is wrong. My guess is GameData isn't in the right place either (or named correctly). This is why you should build your relationship tool one table at a time.

Can a player be in more than 1 clan?

Does a player have certain ships available to them or all ships available to them? Ships A, B, C...X, Y, Z exist, but is it possible that Jim can only use ships G, M and R? Or can Jim choose any of the 26 ships?

Gotcha. How is this? Here is some background to help you: The game is composed of players and clans - each player is in a clan with up to 50 different players in that same clan. I changed "Games" to "Matches" as you were right there. Each match features different clans facing off on different maps, with 7 players on each team which are sometimes the same players but mostly a different group of 7 players all competing in different Ships.

The best analogy I can use is baseball. Except, we field 7 players, all who rotate positions (positions being "Ships"). A baseball team would be a clan in this case. The reason I dont use Team instead of clans is because it would then have a double meaning, (A team in this game is a subset of 7 players within the clan)

 

[plog]

That looks better, but I don't understand the 2 Clan fields in it:

Players.Clan - what does this hold? You said you don't care about opponents, just your players so isn't everyone in Players a member of your clan? What does the Clan field in Playes do?

Matches.OppenentClan - what does this hold? Is it the name of your opponent? Is it related to the Players.Clan field?

 

[ridonc]

That looks better, but I don't understand the 2 Clan fields in it:

Players.Clan - what does this hold? You said you don't care about opponents, just your players so isn't everyone in Players a member of your clan? What does the Clan field in Playes do?

Matches.OppenentClan - what does this hold? Is it the name of your opponent? Is it related to the Players.Clan field?

So to answer you, sometimes the team MAY contain members of another clan. Also, our clan is technically made up of a community of clans, and since we want to track our win/loss rate against individual enemy clans, i decided to add the ClanID to each player if that makes sense

Matches.OpponentsClan is the name of the enemy clan we are fighting
Player.Clan is the Clan that the player is in (we are not tracking enemy players, only their clan (the enemy "team") so Player.Clan is the Clan association of OUR players

 

[Fisherman]

It sounds like you're on the right track with your tables, but you're struggling with how to handle the many-to-many relationship between players, ships, and matches. Here's a suggestion on how you could set up your database schema:

PlayerID (Primary Key)
PlayerName
ShipID (Primary Key)
ShipName
MapID (Primary Key)
MapName
MatchID (Primary Key)
MapID (Foreign Key referencing Maps Table)
MatchDate
MatchID (Foreign Key referencing Matches Table)
PlayerID (Foreign Key referencing Players Table)
Outcome (Win/Loss)
MatchID (Foreign Key referencing Matches Table)
ShipID (Foreign Key referencing Ships Table

With this setup, each match will have a record in the Matches Table, and the MatchPlayers Table will contain records linking the players who participated in each match along with the outcome. Similarly, the MatchShips Table will link the ships used in each match.
This way, you can easily query your database to find out the win/loss record for each player, each ship, and each map by joining the appropriate tables. For example, to find the win/loss record for a specific player:
Similarly, you can adapt this query to find the win/loss record for ships and maps as well.