0

So I have a table with a list of Names, Dob, address etc.

I have a second table where I want to list teams names and their players (9 players per team).

So each row will have 9 names from the table Names. And for the life of me can't figure out how to make a link between them, as one person can be in many teams. (This is why it's weird.)

I can't post the database because of confidentiality.

philipxy
  • 14,867
  • 6
  • 39
  • 83
kieran roberts
  • 1
  • 2
  • 2
    this is possible, but the structure is questionable. Why not have a table with two columns? Team Name and Player -- you'd have 9 rows for every team. – RyanL Feb 15 '17 at 21:37
  • 1
    There is a many-to-many relationship between players and teams. Make a table for each (which you seem to have done already), and a third table called a "junction" table. The junction table lists the Player Id and Team Id for each instance of membership. The junction table will have a one to many relationship with both the players and teams tables. [See here.](https://support.office.com/en-us/article/Create-a-many-to-many-relationship-eeb77a8c-8891-4965-81d6-b2a7329ce449) – MoondogsMaDawg Feb 15 '17 at 21:48
  • Please edit into your question a specific example with what you see as the relevant properties of your design so precise things can be said in answers and comments. – philipxy Feb 15 '17 at 23:43
  • What does "make a link between" mean? Why do you want to "make a link"? Please summarize your relevant "research" (per downvote mouseover info). (You seem to have heard a rumour about something about database design? What *reference(s)* telling you about database design are you using?) – philipxy Feb 15 '17 at 23:47
  • Your title says "join" but your question says "make a link". "Join" is an operator on tables that returns a new table. Please clearly explain what you are trying to do. Preferably with an example with example input and output. – philipxy Feb 16 '17 at 04:57

2 Answers2

0

By a "link" you presumably mean a "FK (foreign key) constraint". A FK is a set of columns in a table whose subrow values must appear as subrow values for some CK (candidate key). (A CK is a candidate for PK (primary key)). FKs (and cardinalities) follow from the criterion for rows going in a table (its predicate) and what situations/states can arise (per business rules). A FK constraint declaration tells the DBMS about a FK.

Each of your team table player name column values has to be a player table name value. So there is a FK from each team table player name column to the player table name column. Eg in SQL FOREIGN KEY Team (player1) REFERENCES Player (name).

You need to read an introduction to information modeling and relational database design. (To query or update re a business situation you need predicates, but not FKs or cardinalities.)

PS When does a row go into the team table? For a given team is there one row, or 9! rows, or 9 to the 9th rows? If just one then which one? (Typically, the subrow that is the "smallest" according to some order.) But those are bad designs. Typically we would have a predicate "team name has player named player". But typically we would have predicates "player with id id has name name..." & "team name has player with id id". But etc. Etc etc. Read a book.

philipxy
  • 14,867
  • 6
  • 39
  • 83
0

You want to make your schema look like this:

enter image description here

It consists of three tables and two relationships (links). You may want additional fields for your purposes. I take it you know how to create tables. I'm guessing you also know how to create links. If not, try asking Access for help with "relationships".

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58