Basketball Project (Please read)

[sedilson]

Okay guys i need your help/ ideas

I'm the process on developing a basketball database, now this database will consist for all the universities in canada, as well as their information regarding both the men's and womens basketball programs, as well as writers and photographers who provide coverage their respective teams.

For each team i would have information on the Head Coach & Assistant Coaches as well as the writers/Photographers for that specific team.

So far my tables are

University Table
(ID, UniversityName, AddressLine1, AddressLine2, Department, City, Province/State, PostalCode/Zip, Country, TelephoneNumber, TelephoneExtension, FaxNumber, AthleticsWebsite, AthleticsEmailAddress )

This table will keep information regarding the university information from this table i will have couple command buttons for Mens Basketball (cmdMensBall) And Womens Basketball (cmdMensBall) which in fact will have informartion regarding Head Coaches and assistant coaches as well phographers & Writers

Coaches Table
(ID, FirstName, LastName, AddressLine1, AddressLine2, City, Province/State, PostalCode/Zip, Country, HomeTelephoneNumber, TelephoneExtension,
WorkTelephoneNumber, FaxNumber, MobileTelephoneNumber, EmailAddress1, EmailAddress2

Photographers & Writers Table
(ID, FirstName, LastName, AddressLine1, AddressLine2, City, Province/State, PostalCode/Zip, Country, HomeTelephoneNumber, TelephoneExtension,

WorkTelephoneNumber, FaxNumber, MobileTelephoneNumber, EmailAddress1, EmailAddress2, PortfolioWebsite, Category - (Photographer/Writer/Both)

Now my problem is that i would like to have the possibility to add more than one coach for a specific University and the same for writers&photgraphers without having to have a seperate table for assistance coach1, assistance coach2, Writer1, Writer2...

Any ideas, suggestions would be welcomed.

Thanks in advance...............

 

[MichaelRed]

Return to the beginning.


Which -in this case- appears to be the nature of RELATIONAL DATABASES.

I would like to offer a suggestion for a reading list or tutorial, but feel it is not my place to do so.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Golom]

Most obvious at first glance is that there doesn't seem to be any way to link a Coach or a Photographer to a University. That is, there is no UniversityID in either record.

To handle the various different coaches I would use either a "CoachType" field foreign keyed to a "CoachTypes" table such as
CoachTypes[tt]
CoachType Description[/tt][tt]
H Head Coach
AD Assistant Defensive Coach
AO Assistant Offensive Coach
WB Water Boy
etc.
[/tt]
If the names are not consistent (for example it may be "Head Coach", "Team Manager", "Top Dog" depending on the team) then just use a "Title" field containing the coach's title.

Similar structures and comments apply to Photographers / Writers, ...

 

[sedilson]

Hey micheal please send that list of tutorials as it will greatly benefit me........

Thanks

 

[sedilson]

Hey guys after short revision i redesigned by tables but i still dont think that they are all finalized it.
Can somebody tell me if this tables would work for what i'm trying to accomplish.

Underline - Primary key
Italics - Foreign Key


tblUniversityInfo

This table will hold all university information

(UniversityID, UniversityName, UniversityAddressLine1, UniversityAddressLine2 Department, City, Province/State, PostalCode/Zip, Country, TelephoneNumber, TelephoneExtension, FaxNumber, AthleticsWebsite, AthleticsEmailAddress)

tblCoaches

This table will allow me to have store information about the coaches

(CoachID, UniversityID, CoachType, FirstName, LastName, CoachesAddressLine1, CoachesAddressLine2, City, Province/State, PostalCode/Zip, Country, HomeTelephoneNumber, TelephoneExtension, WorkTelephoneNumber, FaxNumber, MobileTelephoneNumber, EmailAddress1, EmailAddress2)


tblCoachesType

Type of coaches such values as:
Men's HeadCoach
Men's Assistant Coach
Women's HeadCoach
Women's Assistant Coach

(CoachTypeID, CoachType)


tblCategories
Type of Categories such as

Photographer
Writer
Both

(CategoryTypeID, CategoryType)

This table will allow me to have store information about the photographers & Writers

Photographers & Writers Table[b/]
(PhotographersID, UniversityID, CategoryType, FirstName, LastName, AddressLine1, AddressLine2, City, Province/State, PostalCode/Zip, Country, HomeTelephoneNumber, TelephoneExtension, WorkTelephoneNumber, FaxNumber, MobileTelephoneNumber, EmailAddress1, EmailAddress2, PortfolioWebsite)


Now by me only having one table that will store all types of coaches (mens headcoach womens headcoach) HOW WILL I SEPARATE IT so that when i click on the mens basketball button it will only show the mens basketball. and same thing for the womens section


Any suggestions or ideas

Thanks in advance...........

 

[PHV]

(CoachTypeID, CoachType, CoachMorW)


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[HomeALone]

I like the idea of using the Title to identify the person. My approach would change the tblCoach to become tblPersonnel, The title will identify the position/function of the person. Just in case you need to add a cheerleader or an intern in the future, you would not need to create another table for each. In addition, I move all the contact info to a separate table called tblCommunicationType. This will store all the phones, pagers, cellulars, faxes, email addresses, and you name it as it can grow with our advancing technology in the future. This approach also cutting down the empty holes if one doesn't have that type of communication device.

tblUniversityInfo

This table will hold all university information

(UniversityID, UniversityName, UniversityAddressLine1, UniversityAddressLine2 Department, City, Province/State, PostalCode/Zip, Country, TelephoneNumber, TelephoneExtension, FaxNumber, AthleticsWebsite, AthleticsEmailAddress)

tblPersonnel

This table will allow me to have store information about a person who has a title for the team/university

(PersonelID, UniversityID, Title, FirstName, LastName, AddressLine1, AddressLine2, City, Province/State, PostalCode/Zip, Country)

tblCommunicationType

(PersonelID, NumberEmailAddress, CommunicationType)

PersonelID and NumberEmailAddress will be combined to become the PK and link to tblPersonel. This table allows HomeTelephoneNumber, TelephoneExtension, WorkTelephoneNumber, FaxNumber, MobileTelephoneNumber, EmailAddress1, or EmailAddress2.

Hope this normalization will work for you today and expanding your database without much work in the future!