relational setup help 2

[armstrong722]

If you could be so kind.

I am trying to setup a database for the local little league baseball organization.

I've got some relationships im trying to setup in the tables section.

I'm having trouble with this one

a child has a record birthday address etc "personnel table"

a child is in a certain league minor softball senior etc. "league table"

a child is on a certain team "team table"

I broke out the team tables into separate teams listed for each league (so I have the softball team names in a "softball team table&quot

I can't seem to find the correct linkage or relationship to set up between these different tables

I added a primary key to each table but im not sure i need one?


any help would be apprecitated
thanks
armstrong

 

[Bastien]

You need to add a unique ID to each table to relate them. This one ID should be created in the personnel table (player table, all players should be listed in here) and then the ID should be posted to the team table. I would change the team tables into one table listing all the teams and have the league table list all the leagues. Then create a new table with the team and the player and the league using the unique ID for each player, team and league. Then it should be straight forward to generate whatever queries you need.

PlayerTable TeamTable LeagueTable
playerID TeamID LeagueID
PlayerFirstName Team Name LeagueName
PlayerLastName LeagueID
etc

TeamRosterTable
PlayerID
TeamID



hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[armstrong722]

Bastien,

thanks

I'm not exactly blessed so bear with me. I created the unique keys and 4 tables exactly as above.

But I seem to be hanging up on how to relate them to one another. The relational joins are mysterious to me.
Im really not sure about the join types and their properties.


In the team table I used the number for the league that was assigned in the league table (was that correct?)

regards
armstrong

 

[armstrong722]

I must have tried about 100 combinations...
The other thing that is giving me kittens ... is don't I have to add the team id and league id fields to the players table?

I've got the access bible here for 2000 and i'm not finding the kind of examples I need.

Is there other sources online that anyone might recommend.
I am a once a year user.
thks

 

[svsuthar]

I could help u more if u tell me what kind of db u are trying to make...

Here is my suggestiong for current situation.

Create 3 tables

tblPlayer
PlayerID -- Auto Number Primary key
FName
LName
etc....

tblLeague
LeagueID-AutoNumber Primary Key
League Name

tblTeam
TeamID - AutoNumber Primary Key
TeamName

IF you want to show what player is in which team and league then You would also add LeaugeID and TeamID on player table

then the link will be 1 to many from LeagueID from League table to LeagueID on PlayerTable

same thing with Team Table
1 to many from teamID from Team Table to TeamID on Player table.

so final player table would be

tblPlayer
PlayerID
FName
LName
TeamID
LeagueID
etc....

so the primary key from league and team table would be foreign key on player table.

I hope this helps.

 

[armstrong722]

Family Child Payment league teams
Family ID Child ID paymentID league ID Team ID
father last reg check# leaguename tteam name
father first last cash price league id
mother last first
mother first birthday
street medical
apt meds
city med comments
st general comments
zip league last year
ph-home league this year
ph-business team last year
ph-cell team this year
email gender
insurance
Insurance-id#
field
umpire
concession
fundraising

I am a volunteer for a local little little league.
I am trying to avoid as much processing work and resigning work as possible. therefore I thougth it would be a good idea to develop a database where we could Make rosters. Make
pre entered registrations. Make lists of who must change leagues and go for tryouts based on their age. Find out who has registered up to this point and who has not from last year. email out news notices etc etc.

there is a whole bunch of stuff I know we could do that would make all the volunteers lives easier.

I do have the data entered and it was in excel but its just
very difficult to handle. I imported it to access and now I'm trying to set it up in the most judicious and logical way.

Above is what I think the design should be (?) in order for this database to be sound

I certainly would love to hear from anyone that could tell me if the above looks reasonable and how I might link those tables properly.

thank you
Armstrong

 

[armstrong722]

One other thing that looks formidable is my data is in a flat file. So I must somehow export it and then reimport it
making sure the kids end up in there correct family.
big sighs
armstrong

 

[ZylaWonder]

armstrong722:
I love relational datbases, but I like kids baseball even more! Kids involved in baseball are kids not on the street!! Let's keep it simple.. First the [Child] is kind of your master. You want the other tables to feed information to the [Child] table. In order to do that, we will start this process backwards. (Now that makes alot sense HA! ) We will start with [League] first because [League] is the base table that does not require any information from another table. Look at [Teams], it requires an entry from [League]. The attributes for [League] are:
Field: Type:
[LeagueID] AutoNumber Primary Key
[LeagueName] Text
[Price] Currency

The attributes for [Teams] are:
Field: Type:
[TeamID] AutoNumber Primary Key
[TeamName] Text
[LeagueID] Long Integer (Lookup Wizard)

The relationship between [League] and [Teams] is [LeagueID]. A League must first exist in the [League] table before you could attach it to [Teams]. You could choose lookup wizard for the field type in [Teams]![LeagueID] and when you create your forms a combo box with a list of all of the Leagues would appear. In the form you could then invoke LimitToList=Yes and never be able to choose a League that does not exist. You would first need to enter all of the info into the [League] table. You would create a one to many relationship between [Teams] and [Leagues] many Teams may belong to one League. In the relationship window your choice would be #2: Include all records from [League]...

 

[ZylaWonder]

...After that windy [Teams] vs [League] speech I see in your Child table..[League last year];[League this year];[Team last year];[Team this year]. Actually I saw it before but I wanted to make a point. And it is: Your previous question was "...Kittens...Don't I have to have the team id and the league id in the players table?" Well yeah but... if a team can belong to only one league then if you know the team you know the league. Right? If you choose a league for a team to belong to doesn't that team belong to that league for the entire year? Therefore in [Child] if you choose a team what guarantees you are again selecting the correct league? You would have already done that in the team table. So.. remove [League last year] and [League this year]. Before I bore you even more, digest this and see if it makes sense. Then, if you choose we can talk about last year vs this year.

 

[armstrong722]

Thanks Zyla,

I have mulled!
I need to know the league from the previous year because it helps identify those who are changing leagues. We need to flag them for a tryout for the new league.

I would use age but this does not always work because we will hold back children from moving up leagues for safety concerns etc.

I did manage to change all the data so the child's table now has a family code in it. I have referential integrity and cascade delete related records in place.

I have the fields in the childs record for league, team,
last-league, and last-team and they are populated. But I'm not sure how to connect the league and team tables or if i should just make 1 table with league and team and leagueteamID. I want the person inputting the data to be able to pick the proper team and league.
and then be able to sort as mentioned previously.

How bout that for alot of wind
I hope I was clear

Also is there anyway to post a sample on this site or are people afraid of Microsoft Viruses....etc

thanks
Armstrong

 

[ZylaWonder]

So after I have mulled:
Before we go to last year vs this year:

You will need the Family ID field in the [Child] table! See, mulling is good! I do understand about holding a child back based more on size than age. This happened to my son.

After the "windy" [League] & [Team] thing my next point was if a team remains in the same league then you may want to combine league and team and choose from one list instead of 2. The ENTIRE point is table normalization. You must have a foreign key to connect these tables, i.e. [Child] and [Family]. The fields you choose will greatly depend on the function you will require. A good rule of thumb is to not have redundant fields in your tables. Even though you may end up with a different design on [League] and [Team] it caused you to think about your other tables. Only after mulling of course.

Not to suggest that you remove the Last year/This year from your table, but do you need to track the league and team for more that 2 years? If so you would move the league and team record to another table then create a one [child] to many [league/team] table and be able to track more than 2 years of records.

I think there is a way to post this table, but I do not know how. Find a good book on Access and read about table design and normalization. This will help you more than anything.

Good Luck!!