Nooby question on MySQL table organization

Which method would be more efficient/better style?

[aqutalion]

I'm trying to build a database that contains, among other things, staff and cast for plays. I'm trying to figure out the best way to represent the staff. The two possibilities I have are as follows:

Example 1:

Staff_Table:
Index: Play_ID: Role: Name:
1 1 Director John Doe
2 1 Lighting Jane Smith
3 1 Costumes Bob Jones
4 2 Director John Doe
5 2 Director Fred Johnson
6 2 Lighting Jennifer Doe

Example 2:
Director_Table:
Index: Play_ID: Name:
1 1 John Doe
2 2 John Doe
3 2 Fred Johnson

Lighting_Table:
Index Play_ID: Name:
1 1 Jane Smith
2 2 Jennifer Doe

Costumes_Table:
Index: Play_ID: Name:
1 1 Bob Jones

Obviously, I can't have all a play's staff in a single row, since it's possible for a play to multiple people in a single role. However, for a lot of the roles, it's uncommon to have a person do the same job in more than one play. Making an index table for roles may not be feasible, either, since not every play has the same set of staff roles, and some uncommon roles (one had a horse wrangler, for instance) are only used in one play.

So which would be more efficient/better style? I'm more concerned about the speed with SELECTs than INSERTs.

[rocknbil]

Welcome aboard, aqutalion!

Well, I would use example 1, with some variations:

staff table

rec_id staff_id role_id play_id 
1  1  1  1 

staff DATA

rec_id staff_id fname lname 
1  1   John Doe 

roles

rec_id role_id title  description 
1  1  Actor  Whatever 
1  2  Lighting  
1  3  Costuming  

plays

rec_id play_id title staff_id user_role description 
1  1  test 1  1   blah blah 

This requires more tables and generous use of joins or relational selects in your selects. Why do it this way?

- Look at your production personnel as employees. All of them have a name, all of them have a job, but may be doing multiple jobs under different productions at the same time. So you could say, have an entry for a lighting person in one play and that person is a go-pher in another. So by putting all the staff data in one place, you serve up the maximum expandability of your database structure. You also will not be storing the same data in two places - Jane Doe in the staff table and the director table, for example.

- Selects on numeric values will always be faster than text values. So searching for say, all the actors in a production, you would select the title of actor by two numeric id's instead of a text value. Remember the actual search only occurs on where.

- Note the use of an additional ID field for each table, independent of the auto_increment rec_id field. Too often a (lazy) approach is to use the auto_increment field as the join field for records (done it myself.) If this database ever needs to be moved and at some point a record is deleted from the original table, when the auto increments are regenerated everything shifts up one record. Very bad. Always use a separate join field, not the auto_increment record id.