Where is the Enforce Referential Integrity Option?

[jdanniel]

I'm not sure if it makes sense or not, yet.

What I'm going to do is tell you what I think the third table does, using my terminology and thought process. I'm going to pretend the third table can talk. Here's what the conversation between it and me would sound like.

The third table is saying the following to me:

Hi Jd, I'm Table Number Three. You probably don't understand who or what I am, so let me introduce myself to you, and explain why I am important in your life.

Jd, you just typed in The Wicker Man. Now, you know there are two movies named The Wicker Man. In order to make it easier to know which one is which, we'll assign a serial number, similar to your Social Security number, to each version. Each movie in your database, no matter what it is, will have its own serial number. It will be unique and never duplicated.

The MovieID field will do this for you, and will do this for you automatically.

Okay…you understand that, so let's move on to genres.

The original Wicker Man from 1971 has a MovieID serial number of 10. It can have several genres, right? It's a mystery, it's a horror film, and if you consider British films foreign cinema, then it can be a foreign film. That's three genres.

Here's the issue: You understand two tables, and you understand how several genres can be assigned to a movie.

But you also want to make sure the genre names are always entered in correctly, right? You don't want Science Fiction, Sci-Fi, Sci-fi, SciFi, and Scifi being used. You only want Science Fiction, right?

This is called Consistent Data Entry. Jd, the problem with CDE is that with your two tables, you can't guarantee it. Anyone can type in anything, with no rhyme or reason.

If someone types in Sience Fiction for Star Wars, then when you do a search for Science Fiction movies, Star Wars won't come up. You don't want that, do you?

So…the question is: How do you make sure Science Fiction will always be listed as Science Fiction?

The answer to that question is: Use a third table.

Now…you are probably saying to yourself, "Why?" "How?"

Okay…the third table will deal strictly with serial numbers. You already know a movie title has its own serial number. And don't forget something else, Jd…you created a Genre table that has a GenreID. That means each genre has its own serial number.

With two tables, if you think long and hard enough about it, you'll notice the GenreID field isn't really being used. Where does the GenreID field come into play in your form?

It doesn't! You see the text of the Genre names, but not the GenreID field.

But it's there for a reason, right? If you don't need it, then why have it, right? Okay, so what will it be used for?

It will be used to connect movie titles to genres, using numbers.

Jd, you're probably confused about this, and I understand that.

You're probably saying to yourself, "Okay, numbers…Someone types in Spaceballs. It's assigned the MovieID serial number of 100. It's a comedy and it's science fiction. Someone types in Comedy. Let's say the GenreID serial number for Comedy is 1. "

So far, so good. Keep going, Jd.

"It's also science fiction, and the GenreID serial number for that is 5. So now, Movie # 100 has Genres # 1 and #5. The third table connects these numbers together."

Jd, I'm proud of you. That's good. But you still look like a deer caught in headlights. You're clearly lost here. What's wrong?

"Well, Mister Third Table, I don't understand why there are two primary keys in your table."

Jd, we'll work on that. What else is bothering you?

"Well, I still don't quite see how these numbers prevent someone from typing Sci-Fi or Sience Fiction."

Anything else, Jd?

"I still have no clue whatsoever how to create the form using you. If I want to use the Form Wizard to get Genre names to appear in the subform, what, exactly, is the procedure? I'm not sure what to select, what to join, what to link, etc."

Jd, the answer to these questions are: Kabring and Drew, if they are willing, might be able to solve these problems in your mind for you.

========================================================================================

And that's where I'm at. I'm stuck here, at this point. The method I used above may have sounded silly, but that's exactly what I hear in my mind. Scary, eh?

The two primary keys, the purpose of using numbers in the third table, the subform.

Where do we go from here? But at least do you think I now understand the third table, at least a little bit?

[kabing]

I think you are beginning to get the hang of it, but now I'm confused on one point. Which table are you considering the third table? I've assumed you mean the Genre table when you speak of the third table. But I'm wondering now if you mean the MovieGenre table. We probably need to clarify that, or we're talking past each other.

you wrote:

Well, I still don't quite see how these numbers prevent someone from typing Sci-Fi or Sience Fiction."

As Drew has constructed the database, you don't use a form to enter the Genre names. I think the assumption here is that you, as the database designer, create a predetermined list of genres and enter them directly in the Genre table, before you or other users start entering data about specific movies. (Additional genres can be added later, again directly to the table and not through a form).

When you or another user are ready to enter information about a particular movie, the Movie form is used. In this form, the user never types in a genre name; instead, they choose the genres from the list box drop-down, labled Genre in the form. While the list of genres comes from the Genre table, what is written to the MovieGenre table is the GenreID number for that genre.

In terms of procedure, I'll let Drew spell that out, as Drew is more familiar with list boxes. I'm trying to limit myself in this thread to 1)learning and 2) offering another perspective on explaining the theory behind the procedures. Too many cooks spoil the soup, after all. If either you or Drew think I'm muddying the waters here, I'll back off and just read the rest of the discussion.

kabing

[jdanniel]

Which table are you considering the third table? I've assumed you mean the Genre table when you speak of the third table. But I'm wondering now if you mean the MovieGenre table.

Oops, sorry for not clarifying that. I consider the third table to be MovieGenre, which has two primary keys.

As Drew has constructed the database, you don't use a form to enter the Genre names. I think the assumption here is that you, as the database designer, create a predetermined list of genres and enter them directly in the Genre table, before you or other users start entering data about specific movies. (Additional genres can be added later, again directly to the table and not through a form).

Perhaps this is where all of the confusion lies, including my own. I think I should clarify a few things a bit more. We might have just reached a very critical stage here.

If I wanted to limit the quantity and names of the genres, then I simply would have created a flat-file database with radio buttons. I'd basically be saying to the user, "These are the genres you can work with. Pick one. That's it, end of story."

Or...I could have created a bunch of check boxes, using a field for each genre. It would make doing searches more cumbersome, but at least the user would have a little more freedom to pick multiple genres. And it would make my job easier.

But what if I want the user to have the freedom to use whatever genre names they wish? If they want to keep adding genres as they see fit, on a movie-by-movie basis, should I let them, or should I impose constraints?

For example, let's say they've entered 150 movies, and all these movies have repeated the same genres over and over. But then we come to Titicut Follies, which is a documentary. Let's say the genre name documentary has been used a few times, but this is the first instance of a Cinema Verite documentary.

Do I constrain the user to just use the term "documentary?" Or do I permit the user to now add a new genre named Cinema Verite? If I permit the user to add a new genre, it now becomes his/her choice to do so.

What are my options now? Well, as I see it, I can construct the database as I originally did: Create a predetermined list of genres, and constrain the user. Or, create a database the way you and Drew perceive it, which means making a predetermined list of genres, but permit the user to edit it--via table, not form.

I'm extremely uncomfortable with the latter idea. Why? Because that would turn users into database co-designers. That's not my intent or function. Does that make sense?

I would consider editing the database part of the design process, and the users are not meant to be database designers--JUST users.

Am I complicating things by having this attitude?

Also: This might be an unreasonable desire on my part, but I want the user to enter and work with data purely via FORM. Sure, they may have the ability to view and edit tables, but they won't want to. The form is a SHELL of the table, just as Windows 3.1 was little more than a DOS shell.

Is that a valid analogy?

At this point, I may have to reconsider the importance of consistent data entry. If I want to give the user freedom to create their own genre names, then does that mean we have to sacrifice consistency and normalization? Or do I constrain the user by creating a predetermined list of genres and force them to use only them?

If the answer is sacrifice consistency, then the MovieGenre table is not necessary, am I correct?

If that is true, then I have to do two things:

1. Apologize profusely to you and Drew for taking the time to discuss a third table;
2. Focus on deleting records safely and cleanly.

Or, does the MovieGenre table (aka The Third Table) still need to be made, to enable a safe and clean record deletion system?

Let me conclude by saying something about another database program....I have dabbled with FileMaker Pro, and it has an OTHER option that is pretty funky. I created a flat file database with a few check boxes. FileMaker Pro permits you to create an OTHER box that is editable. If you select the OTHER option, a dialog box pops up that lets you type in whatever you want, such as a genre. Yes, it means you could make a typo or use SciFi instead of Science Fiction, but it sure makes database design easier.

In your opinion, where do we go from here?

Here's where I think I ought to go from here: Compromise. Constrain the user--limit the quantity and names of genres, but create a liberal list of them, so the user doesn't feel SO constrained.

Please let me know if you agree or disagree with this idea. And if you agree, that means proceeding as Drew demonstrated. I would now need to manually enter genres into the Genre table. Maybe then, after doing that, I could understand the MovieGenre table further, since I wasn't able to until just now.

Jd

[jdanniel]

HOLY CRAP!

I think... I think... I understand the MovieGenre table now. (Except for the two primary keys--I mean its purpose.)

I did not understand or realize that the Genre table had to be filled out in advance. Now that Kabring has brought that to my attention, things appear clearer...I think.

Here we go...here's what I think I understand about the MovieGenre table now:

If I enter predetermined genre names into the Genre table, each one is assigned a number.

The MovieGenre table basically kinda-sorta translates the genre name into the genre number, and vice versa, when records are entered into the form.
(If the form is designed properly.)

As far as genre names are concerned, MovieGenre recognizes ONLY the records in the Genre table.

This means it won't recognize or accept a genre name that doesn't have a numerical value assigned to it.

Am I right?

For example, if COMEDY has a numerical value of 2, but you create a record for Annie Hall and try to type in CMDY, the database won't accept it because CMDY has no numerical value, and that's because it's not in the predetermined list of genres?

The net result being: Consistent Data Entry?

Is that the purpose of the MovieGenre table?

If the answer is Yes, then voila...I understand it now. Except for the two primary keys, and how to get a subform up and running.

Oh...one other question: Drew, the way you have the database designed...if I understand it correctly now, then what does MovieGenre do if you do enter a genre name that is not in the predetermined list? Does an error or warning box appear? If an error/warning box does appear, then is there a way to customize it so it says whatever you want it to say? Or is that complicating things more? Or am I still misunderstanding the MovieGenre table?

Jd

[kabing]

What are my options now? Well, as I see it, I can construct the database as I originally did: Create a predetermined list of genres, and constrain the user. Or, create a database the way you and Drew perceive it, which means making a predetermined list of genres, but permit the user to edit it--via table, not form.

I'm extremely uncomfortable with the latter idea. Why? Because that would turn users into database co-designers. That's not my intent or function. Does that make sense?

Also: This might be an unreasonable desire on my part, but I want the user to enter and work with data purely via FORM.

If you decide to do what Drew and I suggest, you do not have to let other users add items to the Genre table. I agree with you, in fact, that they should not do so. The database Drew has built is designed for users to enter data purely via the form, as you have specified.

As far as genre names are concerned, MovieGenre recognizes ONLY the records in the Genre table.

This means it won't recognize or accept a genre name that doesn't have a numerical value assigned to it.

Am I right?

Only partially. The MovieGenre table only recognizes numbers; it doesn't recognize names at all. It does look like Drew set the tables up so that you can only add numbers in the GenreID field that correspond to an exising entry in the Genre table.

But when I talk about choosing a Genre name and Base entering a number, I'm talking about what happens when users enter data through the form.

Try this:

Open the database Drew made; then open the form by double clickin on it
Add a movie, say "Bye Bye Birdie"
Now click on the subform on the right, just under where it says Genre
A listbox will appear; if you click on it, a dropdown will show up and you can pick a genre, say Musical
Click just below the Musical entry and this time choose Comedy from the drop down.
Make a note of the MovieID#
Close the form
Now open the MovieGenre table; you will see two new entries, each with the MovieID number of the new movie, one with the number which corresponds to Musical (probably 1) and the other with the number for Comedy (probably 2) in the GenreID field.

Here's another way to think about the need for three tables:
In relational databasese, tables relate to each other through "linked" fields, right?
These relationships are either one-to-one or one-to-many.
In a one-to-one relationship, one record in Table1 relates to one--and only one--record in Table2; that doesn't apply here.
In a one-to-many relationship, one record in Table1 relates to (or can relate to) more than one record in Table2.

If you have only two tables, one for Movies and one for Genres, you end up with a many-to-many relationship. A record in the Movie table can relate to more than one record in the Genre table. (i.e. Bye Bye Birdie is both a musical and a comedy). But one record in the Genre table also relates to more than one record in the Movie table. (i.e. the Musical record relates to Bye Bye Birdie, but also High School Musical and High School Musical 2.) This, apparently, creates all sorts of problems with data integrity, so it's now allowed.

The solution is to create a linking table. In our case, that is the MovieGenre table. One record in the Movie table can relate to many records in the MovieGenre table. And one record in the Genre table can relate to many records in the MovieGenre table. This avoids the many-to-many conundrum of having just two tables.

Does that help at all?

kabing (with no r )

[jdanniel]

I see things a bit more clearly now.

I went into Drew's sample database, and added a bunch of genres to the Genre table.

I then added some records via the form. You cannot add any genres. Right now, that's good.

Then, I went into the MovieGenre table and looked at the numbers. Interesting...there's a record for each instance of a genre per movie.

I didn't completely understand many-to-many relationships, but I do understand one-to-many. I've read some books that discuss various types of relationships, and most of them discounted and downplayed the many-to-many relationship. As a result, I didn't quite understand it as well as one-to-many.

But now I have a far better vision of things.

What I need to do now is figure out how to create that subform. I need to understand what to select, how to configure it, what items to choose in the Form Wizard.

And I need to understand how to create the list box. Can that be done without SQL, or am I going to need to learn SQL in order to do that?

I also need to make another decision: How many genre names do I add to the list? How many is too many? Scrolling through a lengthy drop down menu may be cumbersome to the user. That's my decision to make, as a database designer.

The subform is what I'll want to focus on next. Thanks for helping me understand the MovieGenre table a bit more.

Jd

PS: Sorry about misspelling your name.

[DrewJensen]

First - I have not caught up with reading all of the intervening posts on this thread yet...

But I'll try to jump in from the last two.

Not sure where to start.

MovieGenre - I took it to be that a movie could have more then one genre. So MovieGenre has a compound key to accommodate this. You can have as many entries for a movie as you like, each entry associates a single genre.

Second the use of a Genre table ( let's call these pick lists for a generic term ) is to restrict the choices for genre yes. But whether you allow the user to add to the list is up to you. This is actually quite common, and it does not IMO make them a designer of the database. In other words as the database designer you defined the 'relationships' and the basic data structures, but the ultimate user of the database defines that actual data. Often in designing business systems the database designer, actually the business analyst, defines certain value lists - for example in an invoicing system there may only be a limited number of payment options, Cash, Check, Credit Card, Net 30. But in other instances the user is supplied a list that they grow as they use the system. This movie genre is a good example of that.

Currently you can add new genre by updating the Genre table and that is fine for some - for others they would add some functionality via a form or a dialog to do this. It is up to the database developer how they want to proceed.

The form and entering Genre - you kind of have the right idea here. The current form is built using a sub-form for Genre and a list box. So to 'associate' a genre to a movie the user selects from the list box, they can not just type in a genre name.

OK - before I go any further.

Lets say you only want to allow a single genre per movie - no exceptions. let's also say that you want your user to be able to select a genre from a list that grows automatically - in other words if the genre is on the list they select it, if they want to add a new genre they simply type in a new one.

Now Base is a relational database right - well, not always. You can use flat databases like dBase and you can also create flat table databases with a relational database engine. So how about we do the above with a flat table structure and see how easy that would be.

I will add a new table to the database - Movie_flat.
The table has the following fields: MovieID, Title, Director, Genre, Location and Running Time

Then I will create a simple form from Moive_flat - just a grid.
The only thing I will do special is in the grid I will select the column for Genre and replace the text control with a Combo Box ( not a list box ).
In the combo box control for a list source I will add this "SELECT DISTINCT "Genre" from "Movie_flat"

Ah - well I see you and Kabring have been talking as I started - paused - and then came back.

But since it is done I'll send this anyway.
Here is is the database with the new table and new form:

movies_2.odb

(21.14 KiB) Downloaded 9512 times

[jdanniel]

Hi Drew and Kabing.

I'm going to take a few days off from this, to clear my head.

Drew, I've already designed a couple of different flat-file databases. It takes me just a few minutes and voila, I'm done. If I want to limit myself, or the user, to one genre per movie, that's easy. I use radio buttons.

In another flat-file database (which Kabing knows about, because we discussed it in another thread), I created a set of check boxes. Each check box has its own field. It permits multiple genres per record. The problem with this technique is that when doing searches or queries, you'd have to include every genre field. If there are 20 genre fields, you'd have to include them all.

But if you want to have multiple genres per movie, and want to streamline queries, this isn't practical. It's the easiest way to create this database, yes, but not the most practical, if queries are going to be done.

That's where a relational database comes in. And I fully understand a two-table relational database. I'm perfectly happy with the one I created, except there are two problems, which we started out talking about. So, we've come full circle.

Those two problems are:
1. Consistent data entry
2. Deleting a record

If I want consistent data entry, then I can go flat-file and accept doing queries with a lot of genre fields. Or, do a three-table database as you explained it.

If I change my mind and decide to stick with a two-table database that does not cater to consistent data entry, then I have only one problem to solve: How to delete a record.

I know how to do that: Delete the genres from the record first, THEN delete the record. That works.

But if you want to delete 50 records (perhaps you sold your DVD's or traded them), each one with three genres, then deleting records becomes cumbersome. I'd have to find a way to streamline that. I suppose I can do that with either SQL or macros, but I do not know SQL and I have no experience writing macros. My gut is telling me SQL would be the better way to do this, because I know SQL has DELETE commands. I just don't know how to write them.

I'm going to take Easter weekend to think about this. Do you think my thought processes are correct?

Jd

[DrewJensen]

OK -

Final statement - just set your Foreign Key relationship to 'Delete Cascade' and I suspect you will have what you want.

Happy Easter weekend then my brother...


He Lives

[jdanniel]

Drew,

Final statement - just set your Foreign Key relationship to 'Delete Cascade' and I suspect you will have what you want.

If this works, then I can consider the database done.

Happy Easter, enjoy the holiday, thank you for your help, and you'll be seeing me here again when I come up with new things to design.

Jd

[RevNomad]

I've been reading this because I would like to create a usable church membership database. May I say this as been fascinating and now my head really hurts!

[DrewJensen]

I don't think I'm doing a good job of explaining it...but have patience and one will come after me that will do a much better job..I promise.

[Villeroy]

jdanniel,
There are no two primary keys in the "MovieGenre" table. A primary key is unique per definition and by double means: There can be only one PK per table and it provides the capability to distinct all records as unique.
The single primary key in "MovieGenre" includes 2 fields, so each combination of "MovieID" and "GenreID" is unique.
In the form you can not classify the movie "Saw" as double-horror. Just try and notice the error message when you submit the record from the sub-form to table "MovieGenre".

[JDHeinzmann]

OMG Drew solved the many-to-many problem in forms the way I have been trying to solve it in my wine tracking database. I have a WineGrapes xref table to do the exact same thing as you did with MovieGenre. I downloaded Movies.odb and it works just like I want mine to. But in my database I am getting a referential integrity constraint "error" when I try to insert a record into WineGrapes. I am using base as a front end to MySQL.

I wonder if you would be so kind as to visit my post on this subject and see if you can offer a solution: Linking many Wines with many Grapes.

Thanks.

JD (a different one)

[simoncoo]

I found the movies.odb file a useful guide for getting what I wanted, but some of the steps along the way were not clear. I have created a video of how to recreate movies.odb from scratch.

http://www.youtube.com/watch?v=GYawYO8u ... e=youtu.be