Database Design Question (1 Viewer)

[Nevsky78]

Dear All,

I have a very basic database design, which I have attached.

There are 2 specific objects I want to create within the database, and was wondering what sort of structure you might suggest, and any links you may have about creating the form and report that I need.

1. I want my form based on the tblActors - this is where I will be adding records. However I also want a subform(?) on there so I can 'attach' as many 'RoleName' from the tblRoles to each 'ActorID'. The subform should have a dropdown list restricted by the 'RoleName' field in the tblRoles.

2. I want to be able to generate a report that can show a list of all the 'ActorID's attached to each 'PlayName' in the tblRoles.

Any help anyone can give me would be very gratefully received! I know if I was just creating the form mentioned in part 1 it would be a one-to-many, but I think point 2 might complicate matters and make it a many-to-many?

I also have very little experience in creating the form and possible sub-form with dropdown list mentioned in point one.

Many Thanks,
Nick

 

[jzwp22]

Since a play can have many actors and an actor can be in many plays, you have a many-to-many relationship. To represent that, 3 tables are needed

tblActors
-pkActorID primary key, autonumber
-txtFName
-txtLName

tblPlays
-pkPlayID primary key, autonumber
-txtPlayName

tblActorPlays
-pkActorPlayID primary key, autonumber
-fkActorID foreign key to tblActors
-fkPlayID foreign key to tblPlays
-RoleName

BTW when you say role name, do you mean a character in the play?

Some other things to think about:
Can an actor play many roles in the same play?
Can the same play happen multiple times (ie. years apart with whole different casts)?
Can multiple actors play the same role within the same play?

 

[Nevsky78]

Hi jzwp22,

Thank you for your response - I think that's where my thoughts were leading.

This database is actually to help me out with a rather complex casting process I have with over 1000 actors applying for over 80 fixed roles across 28 different but fixed plays

And yes, the role name means the character name in a play. With regards to the other points:

• Yes, an actor can audition for many roles in one play
• The casting process with the 'current' 28 plays will only happen once. Next year, there will be 28 new plays with up to 112 new roles
• Yes, multiple actors will be applying for each role

To give a more in depth explanation, this is the process I am trying to develop a database is as follows:

1. We have 28 plays with 76 roles across those plays
2. Actors can apply and state a preference for as many characters as they want across all the plays, therefore one actor can audition for many roles and one role is auditioned for by many actors
3. The data entry form I need would be fairly simple, based on the tblActors, the only complicated bit is how to incorporate the choice of roles one actor wants to audition for
4. Once we have decided which actors we want to audition, I would need a report that would show a list of actors per play

I have no idea if this is possible, but I thought I would ask!

Nick

 

[jzwp22]

The data entry form I need would be fairly simple, based on the tblActors, the only complicated bit is how to incorporate the choice of roles one actor wants to audition for
Once we have decided which actors we want to audition, I would need a report that would show a list of actors per play

The above are definitely possible, but getting there depends on having the correct table structure which is critical to any successful relational database application, so I will focus on that first.

After thinking about it overnight, I see a flaw in the design I proposed earlier. An actor is not auditioning for the play but rather the role. So a play can have many roles and many actors can audition for a role and an actor can audition for many roles.

These two tables stay the same

tblActors
-pkActorID primary key, autonumber
-txtFName
-txtLName

tblPlays
-pkPlayID primary key, autonumber
-txtPlayName

...but we need a table to hold the roles for each play. This assumes that the roles are unique to each play

tblPlayRoles
-pkPlayRoleID primary key, autonumber
-fkPlayID foreign key to tblPlays
-txtRoleName

Now we need to associate the actors to the roles for which they have a preference

tblPlayRoleActors
-pkPlayRoleActID primary key, autonumber
-fkPlayRoleID foreign key to tblPlayRoles
-fkActorID foreign key to tblActors
-longAuditionRank (some sort of ranking system field to identify how the actor placed for the role)

I don't know enough about your selection process but you may want to use the rank field above to rank the quality of the auditions of the actors for each role. Now if an actor auditions for a couple roles and scores #1 for both roles, you have to make a selection as to which role you want them to play, so you may need an additional field to identify the actor selected for each role. Do you plan on having multiple selections per role (lead, understudy etc.)? If so you may have 2 ranking systems one for the audition and one for the actual selection. Continuing the example, if our actor who has auditioned for 2 roles and ranked #1 for each based on the audition, we may want them to be the lead for role A and the understudy for role B. To handle something like this (an actor having 2 rankings), we would use a separate but related table

tblPlayRoleActorRanks
-pkPlayRoleActRankID primary key, autonumber
-fkPlayRoleActID foreign key to tblPlayRoleActors
-fkRankTypeID foreign key to tblRankType
-longRank


tblRankType (2 records, Audition and Selection)
-pkRankTypeID primary key, autonumber
-txtRankType


In the above scenario, I assumed that a role was unique to a play, but that may or may not be consistent with your application. For example, if you had decided to base a play on each of the Harry Potter series of books, the same role name would apply to many plays. If that is the case, then the structure would be as follows

tblRoles
-pkRoleID primary key, autonumber
-txtRoleName

tblPlayRoles
-pkPlayRoleID primary key, autonumber
-fkPlayID foreign key to tblPlays
-fkRoleID foreign key to tblRoles

The tblPlayRoleActors and rank tables would remain the same in this scenario as the earlier one.

 

[Nevsky78]

Hiya,

That's an awesome response, thank you!

I actually don't need to rank - I'm going to keep that side of things quite simple. Once we have selected an actor, I'm just going to include a Yes/No field called "Final Cast". I'm actually not too worried about play association then.

I'll build the tables as you recommend and then try and build the form. Would the form be built on a many to many query? "Based" on the tblPlayRoleActors?

Nick

 

[jzwp22]

Would the form be built on a many to many query? "Based" on the tblPlayRoleActors?

You will need a form/subform design

The main form would be based on tblActors, the subform would be based on tblPlayRoleActors. You would use a combo box to populate the fkPlayRoleID, the combo box would be based on a query (that joins tblPlays and tblPlayRoles).

 

[Nevsky78]

Hiya,

I'm really struggling with the form/subform building. I've attached the updated database - I'm not sure what to do next!

And by that I mean I keep getting a "Control can't be edited; it's bound to AutoNumber field 'pkPlayRoleActID"

Nick

 

[Nevsky78]

Got it!! Brilliant, thanks so much jzwp22!

That is going to save me soooo much time!

Nick

 

[jzwp22]

You're welcome. Good luck with the database.