in reply to Normalisation Question (OT but trust me, perl implementation)

Since actors and directors are both humans, I wouldn't make a different table for actors and directors. I'd make a table people, with columns for the first name(s), last name, and perhaps a birth (and death) date - the latter of course only if you care for that information. And there would be an 'person_id' column.

I'd make a second table 'characters', with character names, and a 'character_id' column.

Then I'd make a third table 'plays', with at least two columns 'person_id' and 'character_id'. One might also add a column 'episode_number'. This gives you the information about which actor played which role(s), and if you add the third column, also in which episodes. One could normalize away that third column, by making the 'plays' have three columns: 'plays_id', 'person_id' and 'character_id', and put 'plays_id' vs 'episode_number' in another table.

In yet another table, 'directors', I'd put people and episode_numbers together.

Abigail

  • Comment on Re: Normalisation Question (OT but trust me, perl implementation)