I can't get the relationships right

[sternsheet]

I am new to related databases, though I've been using FM as a flat file databasse for ages. I am now trying to build my first related database, and I am completely stuck. I attach my efforts so far so you can see where I have got to.

My database will manage a sports team. There is a list of players (about 100 in all) and a list of matches they could play (up to 12). I want to show players' availability for each match (yes or no), which player played the match, and the results.

I am guessing that I need two tables:

table_players

table_matches

I am guessing that I need the following fields:

players_id

players_name

players_contactdetails

match_id

match_name

match_place

match_date

match_player

match_result

What I can't work out is where and how to put the player's availability field. Should it go in table_match, table_player, or both? I could have between 0 and 100 players available for each match. And how do I link all of this together so that I can display on the match layout which players are available, and on the players layout which matches they are available for?

I'm going round in circles in my head, and before my brain boils, I would welcome some help from someone. Please.....

match_database.zip

[comment]

How many players (from your team) play in a single match?

[sternsheet]

Any number of players from 0 to 16 may play in a single match. And a single play may be available (or unavailable) for any number of matches So, from the reading I have done so far, I presume this is a many to many relationship, which is probably why I am so confused by it all...

Thanks for asking. At least some help might be on the way!

[comment]

I presume this is a many to many relationship

Exactly, and this is why you need a third table to join players and matches.

In fact, you have TWO many-to-many relationships here: availability and participation - but I believe you could use a single join table for both (create a join record for each available player, mark the join records of players that participated).

See a basic join table demo here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[mweiss]

Here's what I would suggest:

1. Define a cartesian join (X) relationship between table_matches and table_players. Use this relationship to put an "all players" portal on your matches layout.

2. Inside table_matches, have a field match_availableplayers. You will use this field to store all players_id for every available player for that particular match.

3. In the portal of all players, format the names to act as buttons that do a "Set Field" operation. The "Set Field" operation adds the indicated players ID to the match_availableplayers field (or, if it is already there, will take it out).

4. Finally, add a second TO of table_players to your relationship graph (call it "available players") and define a relationship between matches and available players using the match_availableplayers and player_ID fields. You can then put a portal displaying available players on the Matches layout.

Here's a quick demo.

BTW, you might wan to consider having separate fields for 1st and last name. Likewise a single field for contact info isn't so good; better to have separate fields for phone, address elements, email, etc.

Matches_and_Players.fp7.zip

[comment]

This is a shortcut that works for some applications, but has severe limitations esp. when producing reports.

[mweiss]

Really? I've only recently started using this method myself. Previously I always used a join table for this kind of thing, but this seems so much easier. Could you give me some examples of the kind of problem I am setting myself up for? It's not too late for me to turn back.

[comment]

Two examples:

1. Try to produce a report of available players, grouped by match.

2. Try to add an attribute to the availability of a player, for example the hours they are available.

It *is* a quick and easy way to establish a many-to-many - as long as you can live with the limitations. In this case however, you would need two of those (one for availability, one for participation) - and still no way to record anything other than a simple yes or no.

[sternsheet]

I have downloaded the join table demo, and am working on trying to understand it. The problem is that I don't yet quite grasp the underlying logic of what I'm trying to achieve with a join table, so I am not yet able to replicate it with my Match database.

Maybe the answer is to try to understand one side of the join, and what the join table gives me...

An understanding of the logic would be helpful...

[comment]

Perhaps it will become clearer if you rename Contacts to Players, Organizations to Matches, and Affiliations to Availabilities. Then change the Date field in Availabilities to Participated - it could be of type Number (true/false) or Time (how long they played in the match).

[Saucepan]

hi!

im having somewhat of a simmilar problem and was wondering if you could help me move on? im going to reference til join_demo file since this is kinda what i want to do.

lets say i wanted to enter more parameters into my organization table, as an cost-field. the cost of the "delta" organization might be X dollars. how do i do with the relations to get this show up in the relations table in contacts? and can i also get it so sum up all the costs in a special field next to the contact?

lets for example say adam affiliats with both the alpha and delta organizations. the individual cost of these for adam is 200 and 300 dollars. what i would like here is for the database to list "200" next to alpha, and "300" next delta, in their own "cost" column. this all in the realtionsfield in contacts. then i want the costs to sum up next to adam in a field giving the total cost "500".

can this be done?

[comment]

Yes, it can be done - but the exact method will depend on several factors, e.g.

• Can the same person be affiliated with the same organization more than once?

• Does the cost of an affiliation ever change? If so, what should happen to affiliations created before the change?

I suggest you explain your specific situation, instead of trying to fit an arbitrary model.

[Saucepan]

thank you for trying to answer, i will try to expand my question a little. what im trying to do is to set up a similar customer - product database; "customer" corresponds with "contact" and "products" with "organizations". i should have explained that from the get-go sorry...

• Can the same person be affiliated with the same organization more than once?

the idea here is that the products will show up with each customer - in that table. i guess a customer could by more than one of the same products so lets build it from there! if that happens i guess the best way to solve it would be to introduce a counter next to the product?

• Does the cost of an affiliation ever change? If so, what should happen to affiliations created before the change?

i guess this could change if a customer returns a product - will that be a problem or is it not just as simple aas erase the product from the customer

thanks!

[comment]

I believe you are describing a standard invoicing solution - see:

http://fmforums.com/forum/showpost.php?post/309136/