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

"Actor" and "Producer" are both roles that a Person can take on for a given Episode. An Actor (a Person) might play different Characters in a single Episode, and can even act and Produce.

That suggests separate tables for

Person (person_id, person_details)
Role (role_id, role_details)
Episode (episode_id, episode_details)

with "linking tables"

PersonProducesEpisode (person_id, episode_id)
PersonPlaysRoleInEpisode (person_id, role_id, episode_id)

The "Actor" role gets collapsed into person, since it's implicit when an Person plays a Role in an Episode.

This structure give you good flexibility. It even allows for a Role (a Character) being played by multiple Persons in a single Episode, as might happen when a character ages, or if there's a flashback to the character in his/her younger days.

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

Replies are listed 'Best First'.
Re^2: Normalisation Question (OT but trust me, perl implementation) by Aristotle (Chancellor) on Aug 05, 2003 at 14:06 UTC
Assuming there's a 1:1 relationship between an Episode and its producer, the extra table for PersonProducesEpisode is not necessary. You can instead model an Episode as (episode_id, episode_producer, episode_details) where episode_producer is a foreign key to Person. Makeshifts last the longest.	[reply]
Re: Re^2: Normalisation Question (OT but trust me, perl implementation) by dws (Chancellor) on Aug 05, 2003 at 16:14 UTC
Assuming there's a 1:1 relationship between an Episode and its producer, ... You could assume that, but the cost of being wrong is rather high. By allowing for a 1:n relationship, even if it only happens very rarely, you won't have to rebuild the schema when data arrives about a multi-producer episode.	[reply]