hi
Jim,
CID means contact ID, which is the primary key in the Contact table
When I use "ID" in a field name, it is a long integer data type where the ultimate primary key is a long integer, and usually also an AutoNumber (special form of Long Integer that is a number and automatically gets a value).
When CID is used as a foreign key in a related table, it is also called CID ... but if a related table has more than one CID, then I qualify the second one. For instance, if your related tables is Schools, then CID refers to the contact infomormation for the school. However, the school also might also have am athletic director with a contact record. This second foreign key to contacts could be called CID_AD
> "AD, Asst. AD, Head Coach ect"
This is what I am referring to as a Role. RoleID is the primary key in the Roles table. People can have one or more roles in any circumstance. In the Roles table, I call the text field that describes RoleID just Rol since Role is a reserved word.
Problem names and reserved words in Access, by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html
> "when I add this to Contacts ..."
I would not add a role to the Contacts table, unless it is a default role. In my contact database, I added a generic text field called JobTitle, which doesn't have a table to define what it is, but is really a role. A role is a part that someone plays.
Contacts, in my way of thinking (we're all different, so someone else will have other ideas) is a generic table for information about humans and companies that is connected to tables with related contact information such as phone numbers, address(es) and email addresses. Most tables have a type also ... so if phone, what kind is it? business, personal, during day, evening, vacation, ...
> "Sometimes I think I should stop making this database until I read and study more, but want to keep moving forward as I am learning."
that is great! Good to build as you learn, and remain open and flexbile. Just realize that if you build forms and reports they will also need to be changed if you change the table design. As you would repair a crack in the foundation of a house upon discovery, so should you repair cracks in the data structure (tables, relationships, fields).
> "Redoing the (tblAthleticDirector) renaming to create as Contact table"
imo (in my opinion), "Contact" without qualification should be to a human or company, and not in any specific role.
> "Along the same lines, when I convert the school list box info to real data would I use the school name ID or both?"
when storing the school, in a related table (such as events that the school is involved in), use the SchoolID and/or CID for the school. CID is contact ID. SchoolID is the Autonumber field in the Schools table that is also related to a record in the Contacts table.
One reason to use Schoolid instead of CID, in tables that are related to schools and not general information about people (human or company) is that a table can only have a limited number of relationships. A relationship is defined using the Relationships Diagram and has referential integrity enforced. Referential integrity means that a child record must first have a record in the parent table.
Enforce Referential Integrity (RI) on Access Relationships (cc)