REAL-WORLD COMPUTER PROGRAMMING FOR KIDS of ALL AGES

STEP 47: CREATING THE RELATED TABLES

Welcome back, everyone!

Some of you may have noticed that there were a couple of columns in our Movies_Main Table that I hinted should change, but did not actually change them at the time (in the last Step, #46). These are the director and screenwriter columns. As many directors direct more than one Flick, and many screenwriters have written screenplays for more than one movie, we should store the names in separate tables so as to adhere to the DRY principle.

Thus, rather than have director and screenwriter columns with VarChar(50) data types, these should be director_id and screenwriter_id columns with int data types. I made those changes to the Table, and you can see the result below:

Note: I won’t go into great detail about how I changed those two columns’ names and data types, because this (how to Refactor a Table’s structure in DBWB) was covered in detail in an earlier Step. In a nutshell, though: Make the change directly in the Columns Tab to the column or columns you want to alter (change the “Column Name” value, change the “Column Type” value); select the “Create/Alter” icon, and Wallaby! No muss, no fuss, the Table’s structure has been changed. Unless, of course, you already have Data of the old data type in the Table; that could cause problems, as the existing data probably won’t match the new type of data in the altered column. But since we don’t have that problem – we haven’t populated the Database with any data yet -- we won’t worry about that.

Now, in connection with that Refactoring we did by altering those two columns, we will create separate Tables, not only for Directors and Screenwriters, but also for Actors – and two for Genres, also. This is what we will Create:

ACTORS

actor_id	int

first_name	VarChar(30)

middle_name	VarChar(30)

last_name	VarChar(30)

suffix	        VarChar(10)

DIRECTORS

director_id	int

first_name	VarChar(30)

middle_name	VarChar(30)

last_name	VarChar(30)

suffix	        VarChar(10)

SCREENWRITERS

screenwriter_id	int

first_name	VarChar(30)

middle_name	VarChar(30)

suffix		VarChar(10)

last_name	VarChar(30)

You can see that the ACTORS, DIRECTORS, and SCREENWRITERS Tables are all virtually identical – all but the name of their id Column are identical, in fact. If we wanted to, we could even make them all one Table (named PEOPLE or PERSONS, perhaps), and add another column that would identify which type of person was appearing in the Table in any given Record (that is, an Actor, a Director, or a Screenwriter). True, this would “normalize” the Table structure even more, but sometimes the perfect is the enemy of the good, and this is one of those times. In other words, we should leave well enough alone this time.

A reason why we don’t want to further normalize the database structure in that way is if we did that, we would have to create yet another Table, called a Lookup Table, that would hold only three records with two Columns: personType_id and a personType.These would be the only three Records in that Lookup table:

personType_id	personType

1		ACTOR	

2		DIRECTOR

3		SCREENWRITER

The additional column we would have to add to the PERSON Table (which would replace the three more specific Tables for Actors, Directors, and Screenwriters) would be the fk (Foreign Key) personType_id which would hold a value of either 1, 2, or 3. An elegant design? Perhaps. Tricky? Yes. But/so not worth the degree of extra work and potential confusion it would cause. Also, the fact that same people wear two or more of those hats (Actor, Director, Screenwriter) during their career could confuse things (such as, Ron Howard could appear in that Person Table both as an Actor, with one person_id, and as a Director, with another person_id).

So we’ll leave the Table sctructure as it is, for sanity’s sake (to keep it all from getting too complicated).

Note: You will find that when you are designing software and are “in the flow” or “in the zone,” you will come up with some designs which to you at the time seem elegant and logical (“elegant” is another word often used by programmer types, with a meaning that varies – perhaps significantly -- from the way a fashion designer would use that word to describe a classy model snazzily attired). But when somebody else tries to make heads or tails of your code, they are flummoxed and bamboozled. Even you, coming back to the code later in a cooler frame of mind, may wonder, What was I thinking? Another good acronym to remember in situations like this is KISS, which stands for Keep It Simple, Smartypants!

Here’s how these tables (Actors, Directors, Screenwriters) will work: When an Actor’s name is encountered for the first time, it is appended (added at the end) to the Actors table and assigned the next actor_id value as its unique Primary Key. For example, if there are already 187 Actors in the Table, and “Richard Farnsworth” gets read in for the first time, the Actors Table record for Mr. Farnsworth would be:

ACTORS

actor_id	188

first_name	Richard

middle_name	William

last_name	Farnsworth

suffix	

Note: In most cases, we won’t really need a Middle Name to be provided. In this case we didn’t, really, because there is no other actor named Richard Farnsworth to cause confusion. The same is true of the suffix column – it’s left blank, and normally will be. But think of these scenarios: What if there was another actor named Richard Farnsworth? Then we would have to use the middle name to tell them apart – Richard William Farnsworth and Richard Lionheart Farnsworth, or whatever. Then again, what if there was a father-and-son pair, both named Richard William Farnsworth? That’s where the suffix column is of value, and so we would have  Richard William Farnsworth Sr. and  Richard William Farnsworth Jr. or Richard William Farnsworth II to tell them apart.

So how does this help us? We want to know which movies an Actor performed in, and which Actors are in a given movie. In other words, we will want to query the database, asking, in effect, “Show me all the movies in which Robert De Niro appeared.” Similarly, we will want to query the database regarding all the Actors which were in a particular movie (such as “It’s a Mad Mad Mad Mad World”). How can we do this? How will it even be possible for the Database to give us this information?

The answer: We need to connect this data – the Movies to the Actors. We need, in database terminology, a Relation between the Movies and the Actors. But the main table, Movies_Main, does not even have a single actor_id column any more, let alone the three it started off with (in our proposed Table structure design). How are we going to make this connection/Relation?

I will answer a question (admittedly my own, but possibly it was in your mind, too) with a question. Here’s my counter-question, my rhetorical question: Have you ever heard of a “Many-to-Many Table”? That’s when a Table contains many identical values in one column, and many identical values in another column, but taken as a pair, they are unique.

Minnie-to-Minnie

See if you can see how this “Many-to-Many table” will help us make this connection, or form this relationship, between Movies and Actors:

Actors_Movies

actor_id	int

movie_id	int

Do you see it?

Let’s visualize the data with a few sample records. Say that in the ACTORS table, the Actor assigned the actor_id value of 1 is John Wayne. And say that in the Movies_Main Table the movie Stage Coach is assigned the movie_id of 894, True Grit is assigned the movie_id of 2768, and The Shootist is assigned the movie_id of 3261. Those three Records would appear this way in the Many-to-Many Actors_Movies Table:

actor_id	movie_id

1		894

1		2768

1		3261

Although to a human it looks like pure confusion – just “random” numbers -- it is actually in effect storing the following, but in a more efficent way:

Actor		Movie Title

John Wayne	Stage Coach

John Wayne	True Grit

John Wayne	The Shootist

This is so because the Record, or Row, in the Actors table which has an actor_id value of 1 also contains John (in the first_name column) and Wayne (in the last_name column). The situation is similar with the Movies_Main table: the Record with a movie_id value of 3261 has “The Shootist” as its movie_title value.

By using a Many-to-Many table in this way, an Actor (his actor_id value, that is) can be stored as many times as how many movies he or she has appeared in (dozens and dozens for John Wayne), and a Movie (its movie_id value) can be stored as many times in the ACTORS_MOVIES Table as there are Actors in that movie. The same actor_id could theoretically appear dozens of times, and the same goes for movie_id. But to have the same pair of ids appear more than once in the Table would be redundant, and should be guarded against.

Another Table we need is the Genres Table. Yet we don’t even have a genres column in the main (Movies_Main) table any more. So how is that going to work? We have to do it this way (without having a genre column in the Movies_Main table) because this is another case where there could be multiple genres that apply to any given movie, just as there could be beauxcoup Actors in any given movie (and usually are).

Note: The word “Beauxcoup,” pronounced “Bow KOO” is French for “a lot.” Oh, but how do you pronounce “Bow”? Don’t think of the Bow of a Ship, but of a Bow and Arrow. So: “Bo KOO” might be a better phonetic spelling.

To keep track of the genres into which a movie may fit, we need to create two Tables. A GENRES Lookup Table, and a MOVIES_GENRES Many-to-Many table.

Genres (Lookup Table)

genre_id		int

genre_description	VarChar(30)

Movies_Genres (Many-to-Many Table)

movie_id	        int

genre_id	        int

You should now have seven tables in your MOVIES database:

The GENRES (Lookup) Table would have records such as these:

genre_id	genre_description

1		Action

2		Adventure

3		Animation

4		Biopic (Biographical)

5		Children's  

6		Comedy

7		Coming-of-Age

8		Documentary

9		Drama

10		Dramedy (Drama/Comedy)

11		Historical Drama

12		Legal Drama

13		Musical

14		Mystery

15		Romance

16		RomCom (Romantic Comedy)

17		Satire

18		Science Fiction

19		Slapstick

20		Slice-of-Life

21		Spy

22		Superhero

23		Thriller

24		Western

...and this isn’t even all of the genres – this is a subset of them. The word “Subset” is mathematician and programmer-speak for “some of.”

Which genres do you like best (of those listed above)?

The MOVIES_GENRES Table is another Many-to-Many Table, similar to the ACTORS_MOVIES Table. And so, a few of its Records could be the following:

movie_id        genre_id

894		24

2768		24

3261		24

Using the data in the three records above, in the Many-to-Many MOVIES_GENRE Table, and by referring to the MOVIES_MAIN Table and the GENRES Table, you could deduce that all three movies listed above are Westerns (because their genre_id value is 24, which, according to the GENRES Lookup Table is that type of movie) and you could also figure out that the titles of the movies are Stage Coach, True Grit, and The Shootist (according to the MOVIES_MAIN Table).

But what if a movie can be categorized as exhibiting the characteristics of multiple genres? That’s the whole purpose of breaking this out into a Lookup Table and a Many-to-Many Table, actually (to handle such scenarios). So you might see a few records like this in the MOVIES_GENRES Table:

movie_id        genre_id

3261		1

3261		9

3261		15

3261		24

By looking up the genre_id values in the GENRES Table, you would see that this movie (again, The Shootist, possibly John Wayne’s best (and his last, BTW) flick) is a genre-bender: it is considered Action (there’s a gunfight!), Drama, Romance (John Wayne and Opie Taylor’s mother fall in love (with each other)), as well as being (which is to be expected, considering who the main star is, as well as the title of the movie) a Western.

Again, though, how would you know that the movie_id “3261” means “The Shootist” and that the genre_id 9 means “Drama”? To look these values up manually in those other tables is tedious at best. We will find out later how to query tables like this to instantaneously retrieve the human-readable values you want.

Before we get to that part of it, though, we have to populate the Tables with some actual data. That is the cudgel we will take up in the next Step. So...

Until then!

Earth-shakingly Important Notice: If you have a basic programming question (suitable to an audience of “Kids”), send it to idiolectable@gmail.com, specifying whether you would like your name and location used if it is printed in a future “Step” of this newsletter. If you are a subscriber to the newsletter, you can also leave a question at the bottom of this Step, in the “Comments” section.

If you do not want to give your real name, a nickname is acceptable (the first “Letter to the Editor” of mine that was printed appeared in Rolling Stone magazine, back in the early 1970s, and I signed it “Sylvester” for some reason which I no longer remember).  

Finally, it’s always interesting to see where people are from, so please provide your City or Town and the State it’s in, too (or Province, or whatever the region where you live is called).

To listen to this Step, the audio of it can be found here: