These are great points to consider. THANKS! BTW, I am a novice at this, but
feel confident with the assistance here.
Sometimes, PEOPLE are often in more than one JOB for a given PROJECT. (ie -
a WRITER might PRODUCE and DIRECT a movie) Suggestions for handling
multiple JOBS?
So in a PROJECTS table, I should have a M-M link to the PEOPLE table with
an ID field for WRITER, PRODUCER, DIRECTOR etc?
Should all the ADDRESSES be in a separate table?
Since PEOPLE are quite nomadic in the entertainment biz, would it make
sense to create a "Most Recent JOB" field?
The thought of having separate tables for ACTORS, TALENT AGENCIES, AGENTS,
CASTING DIRECTORS, CASTING STUDIOS, MANAGERS and OTHER_PEOPLE seems to make
sense, but might be overkill? Or properly normalized?
The TALENT AGENCY and CASTING STUDIO addresses rarely change. CASTING
DIRECTORS might hold AUDITIONS at various CASTING STUDIOS, or their own
office. Sometimes the AUDITIONS are with the AGENT at their TALENT AGENCY.
CALLBACKS after the original AUDITION are usually identical info to the
AUDITION, just a new date and time...although it might be elsewhere - such
as a PRODUCER's office.
BOOKINGS are usually at a different location. Tracking payments due/made,
current running projects (conflicts) are requirements to consider in
design.
One major goal is to speed up our locating actors and scheduling
appointments, then emailing and hopefully text mailing appointment sheets
immediately. We will probably require a phone call to them; then confirm
they will make the appointment.
(All MANAGERS need to have access to all unconfirmed with status (eg JOE
left message for ACTOR A @ Time/DATE - waiting for response)
This just came to light - there is an online data feed that will populate
the PROJECTS table, in addition to manual addition of PROJECTS. There are 5
separate feeds in XML format (I will probably hire someone for this
translation)
1) DATA LIST
- display_casting_directory (true/false) this indicates whether the Casting
Director's name should be displayed.
- internal_project_type (string) designates whether this is considered a
commercial or theatrical breakdown, or a memo.
- breakdown_id (int) the unique ID used to reference this breakdown.
- title (string) the title of the breakdown.
- type_name (string) type or category of the breakdown.
- casting_director (string) name of the casting director associated to this
breakdown.
- expiration_date (datetime) the date/time the breakdown is considered
archived.
- allow_electronic_submission (true/false) indicates whether the breakdown
allows electronic submissions.
- union_name (string) SAG, AFTRA, etc.
- date_published (datetime) the date/time the breakdown was released.
- shoot_location (string) the location where the project will be shooting.
- parent_breakdown_id (int) if the breakdown is an addendum it will have a
parent.
- start_date (int) the earliest date listed as the start date or when the
project will begin filming.
- submitted_on (true/false) indicates whether this talent rep has submitted
on this project.
2) DATA DETAILS
- internal_project_type (string) designates whether this is considered a
commercial or theatrical breakdown, or a memo.
- display_casting_directory (true/false) this indicates whether the Casting
Director's name should be displayed.
- parent_breakdown_id (int) if the breakdown is an addendum it will have a
parent.
- title (string) the title of the breakdown.
- casting_director (string) name of the casting director associated to this
breakdown.
- date_entered (datetime) the date the breakdown was first created.
- expiration_date (datetime) the date/time the breakdown is considered
archived.
- type_name (string) type or category of the breakdown.
- usage (string) how the breakdown is used (commercial, print, etc).
- union_name (string) SAG, AFTRA, etc.
- left_heading (string) free form text entered for display on left column.
- right_heading (string) free form text entered for display on right
column.
- submission_instructions (string) instructions on how to submit your
clients
- submission_address_1 (string) line 1 of submission address.
- submission_address_2 (string) line 2 of submission address.
- submission_address_3 (string) line 3 of submission address.
- submission_city (string)
- submission_state (string)
- submission_postal_code (string)
- submission_country (string)
- submission_phone (string)
- submission_fax (string)
- submission_email (string)
- submission_alt_instructions (string) alternate instructions on how to
submit your clients
- submission_alt_address_1 (string) line 1 of submission address
(alternate).
- submission_alt_address_2 (string) line 2 of submission address
(alternate).
- submission_alt_address_3 (string) line 3 of submission address
(alternate).
- submission_alt_city (string)
- submission_alt_state (string)
- submission_alt_postal_code (string)
- submission_alt_country (string)
- submission_alt_phone (string)
- submission_alt_fax (string)
- submission_alt_email (string)
- special_notes (string) displayed below submission/alt instructions.
- date_notes (string) used for explanation of interview/callback/start
dates or other misc dates types.
- pay_rate (string)
- production_company_name (string)
- advertising_agency_name (string)
- records (string)
- shoot_location (string) the location where the project will be shooting.
- conflicts (string)
- allow_electronic_submission (true/false) indicates whether the breakdown
allows electronic submissions.
- date_published (datetime) the date/time the breakdown was released.
- parent_title (string) if the breakdown is an addendum this holds the
value of the title of it's parent.
- parent_date_published (datetime) the date/time the parent breakdown was
published.
- breakdown_number (int) not in use but used for reference of where it fits
in the addendum chain.
- additional_notes (string) displayed below the roles.
3) PRODUCTION STAFF
- first_name (string) first name of the staff.
- last_name (string) last name of the staff.
- role_name (string) role the staff plays (Photographer, Director, etc.)
4) DATA DATES
- display_date (datetime) the date/time value.
- datetype (string) Interview Dates / Callback Dates / Start Dates
- notes (string) Any notes associated to the dates. Not in use.
5) DATA ROLES
- role_name (string) the name or title of the role.
- role_description (string) the description of the role.
- role_id (int) the unique ID used to reference the role.
- role_display_order (int) number representing the order in which the role
is to be displayed.
- category_name (string) name of category the role belongs to (if
applicable).
- category_description (string) the description of the category.
- category_display_order (int) number representing the order in which the
category is to be displayed.
- attachment (string) filename that is attached and ready for download for
the role.
And I am sure I am missing a lot - but this is a start. The group wisdom is
welcome!
I realize the normalization is essential to a decent application and really
appreciate the online assistance. TIA (Do programmers have more
"integrity?" <g> ) Thanks, Rick
Some random thoughts that may help refine the structure.
1) Most of the entities you're modelling are PEOPLE: Actors, Agents,
Assistants, Casting Directors, Casting Assistants, Directors, Producers,
Writers, Managers. It's possible for the same person to appear in more
than one of these roles. So there's a strong prima facie case for a
Persons table for the data about the people, rather than putting them
into multiple tables; a Roles table (with records for Actor, Agent,
Director, etc); a PersonsRoles table to store the roles that each Person
is known to fill (e.g. if Mr X is a Director the fact would be stored by
a record in PersonsRoles); and a PersonsRolesProjects table to store the
roles that each Person is filling in relation to individual projects.
2) Some entities are businesses or parts of businesses (e.g. Agencies,
Departments). Any time there's may be more than one person involved in
the other end of a transaction (e.g. an Agent who's the nominal party
and the assistant/s who make the bookings) and you need to keep track of
them, you need to store the transaction as with the business (or
department) in question and link the individual(s) to the transaction.
So you could use something like this table
tblBusinessUnits
BUID - autonum PK
BUName
BUAddress
...
BusUnitParent - self-join to BUID
Things get messy if one kind of transaction is sometimes with a business
and sometimes with an individual. (E.g. some Agents are companies, some
are sole traders; some actors have personal management companies - if
only for tax reasons - and some don't). In that case you may need to
abstract one stage further and have a "top-level" entity such as
LegalPerson, subclassed into Businesses and Persons.
3) Auditions, Callbacks and Bookings all sound like varieties of the
same entity: Events.
4) Skills: this is definitely not a table with 150 fields. You'd have
something like this:
tblSkills
SkillID
SkillName
SkillDescription
with however many records are needed, and
tblPersonSkills
PersonID (or LegalPersonID)
SkillID
SkillLevel (e.g. Some, competent, expert, specialist)
to store who is how good at what.
5) For Breakdowns, you presumably need Counsellors<g>.
Gemstone Talent
www.GemTal.com