newbie just starting tables/normalization

[gtm1]

Trying to get a normalized structure for Talent Management company

We are TALENT MANAGERS that represent TALENT (actors, singers, dancers,
models, writers, etc )

We usually work in conjunction with AGENTS at AGENCIES to get the ACTORS
AUDITIONS with CASTING DIRECTORS (CD's) that hopefully turn into CALLBACKS
and BOOKINGS.

Talent may have zero, one, or several different AGENCIES for Commercials,
TV, Film, Theater, Print, etc.

We get breakdown info via computer websites that will ideally populate the
PROJECT tables.

When an appointment notice is received, we want to send the appt info to
our talent via email and text message with a flag noting when the talent
confirms (they text, email or telephone their confirmations) and we have to
relay that confirmations or decline to the Casting Directors)

A few confusing items - the biz is comprised of nomads ie
CD's change locations and phone #'s regularly
CD may have separate address with different contact info for each PROJECT
AGENCIES are fairly stable, but AGENTS could use revolving doors (switch
AGENCIES, move to MANAGEMENT, etc)

AUDITIONS (or other APPOINTMENTS) are often at CASTING STUDIOS - often a
different address from the CD
CALLBACKS may be at a same or different location

PROJECTS are jobs for TALENT who are AUDITIONED by CD's
PROJECT INFO has details such as DIRECTOR, PRODUCER, WRITER - (may be same
person wearing several hats - or totally different) Also Project Type and
Payrate
PROJECT ROLES are the specific roles being auditioned for, inclusive of
details such as AGE, SEX, ETHNICITY

Our TALENT runs the gambit from 15 day old babies through retirees. There
may be siblings or children - some represented, some not - but it would be
useful to list family members for any contacts. (some contacts will be
personal, business, clients or all the above)

Identical TWINS, TRIPLETS and higher order multiples are like gold in the
biz and important to note this particular sibling relationship

When a job is booked, we track payments due - we bill/invoice for non union
jobs, and wait a month for union jobs to pay. Commercials usually have
residuals that we track based on cycles and holding fees.

Based on the above info, it appears there are

tblContacts
ContactID AN PK
FirstName
MiddleName
LastName

tblFamilyMember
FamilyMemberID AN PK
FamilyMemberType

tblMultiples
MultipleID [2-6 representing twins through sextuplets?]
MultipleType [Identical, Fraternal]

tblContactType
ContactTypeID AN PK
ContactDesc

tblAddresses
AddressID AN PK
StreetNumber
StreetName
SuiteNumber
City
State
Zip

tblBusinessName
BusinessID AN PK
BusinessName
BusinessType

tblTalentData
TalentDataID AN PK
DOB
HT
WT
HAIR
UnionMembership
etc

tblTalentSkills
TalentSkillsID AN PK
SkillType
SkillLevel

tblUnions
UnionID AN PK
UnionName

tblProjects
ProjectID AN PK
ProjectName
UnionAffiliation

tblProjectDetails (combine w/Projects or separate table?)
ProjectDetailsID AN PK
Producer
Writer
Director
AuditionDates
CallbackDates
ShootDates
ShootLocation
etc

tblProjectRoles
ProjectRolesID AN PK
RoleName
RoleRequirements (age, ethnicity, sex)

tblAppointments
AppointmentID AN PK
ApptType
ApptLocation
ApptTime

Then there are a few more tables for the accounting -
billing/AR payment tracking that I haven't gotten to yet - open to
suggestions.

FK's are missing - not sure how to incorporate, inclusive of relationships,
but it's apparent they are required

Should be able to create stats for clients - # of submissions, # auditions,
# callbacks, bookings etc, plus %'s and $ amounts with properly normalized
data.

relatively small amount of data -
There are about 2,500 total CD's & Agents.
Up to about 1,000 Actors
Total # contact names less than 25,000
Projects and ProjectRoles could grow to 100,000 or more, but obsolete
Projects & ProjectRoles can be removed regularly (when job is booked, or
after a few months)

Less than 20 users envisioned and rarely more than 5 at any given time.

I'm sure there are lots of missing pieces, so fire away with questions and
suggestions, bearing in mind my access virginity. (but quick learner)

Thanks in advance

 

[mnature]

Quite an impressive start. However, for a few hundred dollars, there is
canned software out there that can do most of this, and is ready to use. I
found one (no guarantees on this, it was just a googled entry) at
http://www.inentertainment.com/ that seem like it could do most of what you
are wanting. I think you can download a demo of it to try out.

However, if you do want to continue this programming project, the next step
is to figure out how your tables interact. You have a good start in figuring
out what you want to track. I would suggest printing out the sample tables
you show, spreading them out on a table, and seeing if there are natural
groupings of information. As far as foreign keys, it is very possible from
your description that you are going to need some linking tables, which will
include several of the primary keys from two other tables as foreign keys in
the linking table. Whenever there is a condition where there could be
multiples from either table to link to the other table, you need a linking
table which will establish all of those many-to-many links.

 

[gtm1]

Thanks for your response.

If it was only a few hundred dollars, we would already have purchased <g>
By the time one purchases all the modules and users licenses, it exceeds
$5,000 and still doesn't come close to our desires, nor will it allow
tweaking. There are other solutions require annual fees in the same
ballpark with similar shortcomings

I'd rather spend the money to get exactly what we want...create something
that exceeds the current available packages and be tweakable

There is a lot more we anticipate our program doing - we have a 13 page
outline - but it seems nothing can be done until a solid normal table
structure is accomplished.

I will look into linking tables and try to figure out FK's - this is where
I'm stumped so far

 

[mnature]

Since you are a beginner (but a quick study), I would suggest reading at
least one book before getting too far into your project. Database Design for
Mere Mortals is by Michael J. Hernandez, and is an excellent introduction to
relational database design. A trip to the bookstore or to Amazon can provide
you with several other titles of beginner books for learning Access.

The primary stumbling-block to creating a good, viable, and tweakable
database is to create a normalized table structure. This cannot be
emphasized enough. It is better to spend four months to a year getting the
tables right, than to jump in with an almost-right design, only to have to
throw it out a year later.

 

[gtm1]

Thanks for the response. Already went through Hernandez and applied all
that was understood - that's where writing the narrative started (along
with urging from other newsgroup members) guess the joining table escaped
my understanding. I was more concerned about not repeating data <g>

Additionally, viewed several training videos and a few books inc
Access2003 VBA

BUT...the unusual nature of the inter-relationships was not covered in any
material I understood. This project has taken well over a year to start
while getting part time education - lurking and reading. Thought it would
be advantageous to get the basics started and bring in a professional
partner to do stuff in minutes that would take me hours.

One kernel gleaned in all my reading is the initial structure dictates the
potential success or inherent failure of any DB system. To establish a less
normalized set of tables is much easier for me to comprehend, but less
efficient and wrong by most accounts.

FWIW - we have been using ACT! for several years and hating every minute of
it. Slow and unable to accomplish most of the requirements for a
replacement system.

 

[BruceM]

Since you mentioned foreign keys a few posts ago I will jump in here and see
if I can clarify a little. The PK is established in table design view. The
FK is a field in another table, of the same data type as the PK. If the PK
is Number, the FK is number; if the PK is text, so is the FK, and so forth,
except that if the PK is autonumber, the FK is number.
An example may help. An agency may have many agents. The agency table
includes a field that will be different for each agency record (which
consists of name, address, phone, etc.). If the table is tblAgency, it is
convenient to refer to this field as AgencyID. It can be autonumber or
anything else you choose, but I will say that it is autonumber. By
designating the field (in table design view) as the PK you are prevented
from adding two records that have the same PK.
Now you need to include the agents, so you make a separate table (tblAgent)
with fields for FirstName, LastName, and so forth. You will also have a PK
field (AgentID) and a FK field (it is convenient to call it AgencyID) that
will be of the Number data type.
When you click Tools > Relationships to open the relationships window, you
can select tblAgency and tblAgent. Drag AgencyID from one table to another,
and let go. Click Enforce Referential Integrity. Now you have a
one-to-many relationship between Agencies and Agents (each agency may have
many agents).
A common way of handling these related tables is to create a form
(frmAgency) based on tblAgency, and a subform (fsubAgent) based on tblAgent.
For now add controls for all fields, including PK and FK. Set the Default
View of tblAgent to Continuous (one record above the other) for now. With
frmAgency open in design view, drag fsubAgent onto it. When you switch to
form view, you can add an agency to the main form, and agents to the
subform. You will notice that each Agent record that is added to tblAgent
by way of the subform contains the same number in its FK field as the main
record has in its PK field. You didn't have to add them. The relationship
took care of that.
Of course, this is a very simple situation. An actor may have several
agents (or agencies), and an agency (or agent) will have a number of clients
(one would hope). One agent at an agency may represent an actor for stage,
and another agent at the same agency may represent the actor for TV. And so
forth. All I'm really trying to do here is to give some insights into FK.

 

[mnature]

If you have the second edition of Hernandez' book, then go to page 361,
otherwise look up Many-to-Many in the index.

For instance, if you have a table of Talent, and a table of Agents, and the
Talent could be associated with several Agents, and several Talents could be
served by the same Agent, you will need a linking table to join them. A
linking table will allow you to "link" any Talent with any Agent. The
linking table can grow to be quite large, because of this. However, you will
not be looking at the table to find out which Talents belong to an Agent, or
to assign them. You will use a form and subform, with the main form allowing
you to choose an Agent, and the subform then listing which Talents are
contracted to that Agent, and allowing you to add more Talents as necessary.
You can also use a form and subform to tell you which Agents are being used
by a particular Talent. The linking table, then, has to include foreign keys
that are the primary keys of the Talent and Agent tables. That is what ties
them together.

For instance, the tables could look like this:

tbl_Talents
TalentID (PK)
TalentInfo

tbl_Agents
AgentID (PK)
AgentInfo

tbl_TalentAgents
TalentAgentID (PK)
TalentID (FK)
AgentID (FK)
TalentAgentInfo

In your case, you may have a linking table that also designates what sort of
Jobs a particular combination of Agent and Talent would take on. In that
case, you would have an additional foreign key that ties to the primary key
of a table that lists the types of Jobs that your Talents take on.

One mistake that new Access programmers often make, is to think that the
tables are the information. Tables contain data. Information is what you
gather from the tables, using queries and forms. Normalization makes it
easier to gather good information. Well-designed forms make it easier to
enter good data.

 

[gtm1]

Thanks Roger, BruceM and mnature for the excellent tutorials. You have
enhanced my understanding (which I thought was better <g>)
I'll have another go at it and get backacha.