|
|
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.
|
|
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
|
|
|