Database Design

[Harvey]

Hi, I wonder if anyone can help. I am trying to create a bowls pairs league
database. At the moment I have 6 tables, league, fixture, game detail, team
names, players names & score which are linked in relationships league (can
have many fixtures), fixtures (can have many game details). The league table
consist of league ID (primary key) & league. The fixture table consist of
fixture ID (primary key, league foreign key, date, league, team 1 & team 2
(both team 1 & team 2 are look up's from the team names table( I know people
say you should not do this but I can't get my head round it to do it any
other way??)). It is the same with the games detail table which consist of
game detail ID (primary key), fixture ID (foreign key), names 1, names 2,
score, names 3, names 4 & score. Again the 4 names are from look up's, as are
the score's!!! My main problem is that players (names) do not play every game
& not always with same partner. I have tried joining the names with a union
query?? (is this the right way to do it or is their some other way. By the
way, the fixture game detail consist of 2 teams, each consiting of 2 pairs,
the games are 21 up (hence the score table). If say team 1 has both winners
then they get 3 points, one for each winner & 1 for the aggrigate Hope
someone can help me in this matter.

 

[Fred]

My head was spinning with confusion after reading this. My first guess is
that your issue is a cart-before-the-horse situation. (plus I don't know
bowling) A couple thoughts that might be helpful.

Recommend thinking through, which of your things are ENTITIES that you want
to record (these will become your tables), which things are merely one-to-one
attributes of those entities (those will be additional fields in your
tables), and which things can be recorded simply as a relationship between
entities.

The main work of creating a relationship is placing a FK (with the correct
data) in a table and then the proper data into the FK. Then the "join" /
union is just the last 1% of the process.

I wasn't able to absorb you post well enough to definitively know your
situation, but my fist guess is that leagues, fixtures, teams, pairs and
players. are your entities/tables, and in that "heirarchy" meaning that each
subsequent items is the "many" in a many-to-one relationship. And that
scores are attributes (1 to 1 type data) of games and thus fields in the
games table.

Hope that helps a little.

Fred

 

[Harvey]

My head was spinning with confusion after reading this. My first guess is
that your issue is a cart-before-the-horse situation. (plus I don't know
bowling) A couple thoughts that might be helpful.

Recommend thinking through, which of your things are ENTITIES that you want
to record (these will become your tables), which things are merely one-to-one
attributes of those entities (those will be additional fields in your
tables), and which things can be recorded simply as a relationship between
entities.

The main work of creating a relationship is placing a FK (with the correct
data) in a table and then the proper data into the FK. Then the "join" /
union is just the last 1% of the process.

I wasn't able to absorb you post well enough to definitively know your
situation, but my fist guess is that leagues, fixtures, teams, pairs and
players. are your entities/tables, and in that "heirarchy" meaning that each
subsequent items is the "many" in a many-to-one relationship. And that
scores are attributes (1 to 1 type data) of games and thus fields in the
games table.

Hope that helps a little.

Fred
Sorry for not getting back earlier Fred, but have been laid up with a bug. Anyway, before I start, can I thank you for the reply. I will try to simplify things (if that is possible with me!!) My main problem is that I have 4 fields in my game detail table, name1, names2, names3 & names4. All of these fields are drawn from 1 table, players names via look ups. My main question is, is it possible to combine the 4 field into 1 via a query, i.e. 4 columns of say 10 rows into 1 column of 40 in the query. Once again thanks for the reply.

 

[Fred]

Hello Harvey,

Taking your question literally, I don't know how to do that and it is rare
because usually only a mis-designed database would require something like
that.

- - -

Assuming that there is no difference between the 4 fields in your games
table (and your "combining" question supports that presumption) changing
your design to proper normalization will make what you want to do easy.

Make a new "participation" table which has a record for each instance of a
person participating in a game. It can have as few as two fields:

PersonIDnumber
GameIDnumber

So, recording a typical game would add 1 record to your games table and then
4 teeny records to your participation table.

After copying/moving the participation data over, and backing up, delete
those 4 name fields from your games table.

Now your lists are "pre-combined" and you can select for whatever game or
people criteria that you wish and get the list that you describe.

Sincerely,

Fred

 

[Armen Stein]

Taking your question literally, I don't know how to do that and it is rare
because usually only a mis-designed database would require something like
that.

You can use a Union query. That joins tables (in this case your one
table four times) "vertically" instead of "horizontally". That would
give you your desired result of 1 column of 40 rows.

Look up Union queries in Help to see how to do it. You can't design a
Union query in the query designer - you'll need to switch to SQL view.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Harvey]

You can use a Union query. That joins tables (in this case your one
table four times) "vertically" instead of "horizontally". That would
give you your desired result of 1 column of 40 rows.

Look up Union queries in Help to see how to do it. You can't design a
Union query in the query designer - you'll need to switch to SQL view.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Thanks for that Armen, I tried the union query and it gave me near perfect results. I say near because at the moment I have a list of 71 players who have played in 25 games, not all the players have played equal games, some have played 1, some 2, and others even 4 or 5. Out of the list of 71 names in union query about 80% of the information was correct. Any ideas why?

 

[Armen Stein]

Thanks for that Armen, I tried the union query and it gave me near perfect results. I say near because at the moment I have a list of 71 players who have played in 25 games, not all the players have played equal games, some have played 1, some 2, and others even 4 or 5. Out of the list of 71 names in union query about 80% of the information was correct. Any ideas why?

Hi Harvey,

First, what newsgroup client are you using? Your reply is prefixed
with a > symbol, which mixes it in with quoted text. And your lines
aren't word-wrapping, making them hard to read.

Anyway, back to your question. When you say that 80% of the results
are correct, what's wrong with the 20%? That sounds suspiciously
close to one of your 4 Select statements in your Union query. Have
you double-checked it to make sure that you are querying all four
different columns, one for each Select?

If you like, you can indicate which player the record came from by
using literal field values, like this:

Select Player1, "Player1" as PlayerNumber From MyGameTable
UNION
Select Player2, "Player2" as PlayerNumber From MyGameTable
UNION
....etc.

Also, Unions by their nature remove duplicates, unless you specify
UNION ALL. You might want to try this to see the difference.

Hope this helps you figure it out,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Harvey]

Hi Harvey,

First, what newsgroup client are you using? Your reply is prefixed
with a > symbol, which mixes it in with quoted text. And your lines
aren't word-wrapping, making them hard to read.

Anyway, back to your question. When you say that 80% of the results
are correct, what's wrong with the 20%? That sounds suspiciously
close to one of your 4 Select statements in your Union query. Have
you double-checked it to make sure that you are querying all four
different columns, one for each Select?

If you like, you can indicate which player the record came from by
using literal field values, like this:

Select Player1, "Player1" as PlayerNumber From MyGameTable
UNION
Select Player2, "Player2" as PlayerNumber From MyGameTable
UNION
....etc.

Also, Unions by their nature remove duplicates, unless you specify
UNION ALL. You might want to try this to see the difference.

Hope this helps you figure it out,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen, thank you very much for that, union all did the trick. Now I have perfect players info i.e. games played, won & lost. Like you said, union on it's own removed info. Once again thank you very much. Also can I thank Fred for his input. Cheers everyone.