re: Any Microsoft Access Experts?

quote:

---

quote:

---

And are you making a separate table for each team?

---

Yup. Because each team table will have different competitions, team finishes, etc.

---

That sounds like all the more reason to lump all of the teams into the same table so you aren't making queries that have to check several tables for stuff.

quote:

---

quote:

---

If so, you might consider making a 'teams' table that gives each team an ID (so it would just need an ID column and a name/description column), and then a table called something like 'alumni_teams' that just has two columns, one for alumni ID and one for team ID, which as someone else mentioned would just reference the foreign keys in the alumni and teams tables.

---

You lost me. Could you explain it to me like I'm in pre-k?

---

OK, but I'm no DBA by any means, but I do have some experience working with (mostly mysql) databases. And I don't know exactly what type of teams or competitions you are talking about, so all of this may not applye, but...

The basic idea is if you can describe a group of things as a certain 'type' or broadly categorize them so that they all have the same set of attributes, they should all go in one table. For example, I would make a 'teams' table that gives each team an ID, a name, a description, or whatever info a team should have. If the teams can "belong" to different types of sports or games or what have you, then I would make a 'sports' table as well.

The basic premise of relational databases is that information in one table is linked to information in another table by using "foreign keys", which is what you are trying to do by putting the alumni ID in your teams tables. This allows you to create different types of relationships: one-to-one, one-to-many / many-to-one, and many-to-many. If each alumni will only belong to one team, then that is a many-to-one since many alumni will be on one team, and looking at it from the team perspective one-to-many. If they can each belong to several teams, though, then you have a many-to-many relationship, which simply requires a third table that links IDs from each of the other two. So, no matter how many new teams are formed, you never have to create a new table. Instead, you just add a record to your teams table and you can start linking alumni to it via the third table ('alumni_teams' in my previous post).

Taking this further with your situation, you might create a 'competitions' table that gives each competition an ID, name, descriptive info, date, or whatever. And then you would have a 'teams_competitions' table with just two fields for the foreign IDs: team_id, and competition_id.

If you break things out like this, you aren't duplicating any information, and you are making it easier to run arbitrary queries later on that you may not think of now. For example, you could query the 'alumni_teams' table for a specific team ID to build a list of alumni who are on that team (the database-y way to do what you were attempting by making separate tables for each team). Or, you could query the 'teams_competitions' table to build a list of teams that competed in a particular competition, or build a list of competitions that a particular team competed in.