Forum Moderators: open
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.
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.
