Extra table for that?

[Niniel]

Hello,

I'm trying to update this movie database. The original was mostly a flat db,
so I'm trying to do things the right way this time.
Amongst other things, the db tracks if a movie is based on a book, and if
so, what the title of the book is.
The text field [OrigBook] basically has three types of information - either
it is empty, or it has the word "same" in it to indicate that book title and
movie title are identical, or the real book title is listed.
Quite often it's "same", so I'm wondering how to model this correctly. I
can't see how to do it any other way than it is right now, but maybe somebody
here can give me some advice.

Thank you.

 

[Jerry Whittle]

I would put the actual book title in it where there is a book. Why?

Movies sometimes have their titles changed.

There is a book with the title "Same" and many books with "Same" in the title.

 

[Steve]

Use an Update query with "Same" as the criteria for BookTitle and update
BookTitle to the name of the movie.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Niniel]

Thank you for your replies.
I was tempted to say that you had missed the point of my question but on
second thought, maybe you didn't. Ok, then I'll just stick with what I have
instead of trying to make it unnecessarily complicated.

Use an Update query with "Same" as the criteria for BookTitle and update
BookTitle to the name of the movie.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Hello,

I'm trying to update this movie database. The original was mostly a flat
db,
so I'm trying to do things the right way this time.
Amongst other things, the db tracks if a movie is based on a book, and if
so, what the title of the book is.
The text field [OrigBook] basically has three types of information -
either
it is empty, or it has the word "same" in it to indicate that book title
and
movie title are identical, or the real book title is listed.
Quite often it's "same", so I'm wondering how to model this correctly. I
can't see how to do it any other way than it is right now, but maybe
somebody
here can give me some advice.

Thank you.

 

[Niniel]

Hello Jamie,

Thank you for your reply, although I have to say, it sounds rather
complicated... and honestly, I'm not sure I could program Access to pull that
off.
But somebody suggested another idea, and I'm leaning towards doing it that
way: to have a tblBooks, and a junction table that records the relationships
between movies and books. That way, I wouldn't have to bother with movies
that are not based on books, and could easily accommodate movies that are
based on more than one book, and track books that served as the basis for
more than one movie.

 

[George Nicholson]

A tblBooks & junction table route would also allow you to include "(None)"
(and maybe "(Unknown)") as a book title and make it a required field in the
Movie table. That would remove the ambiguity of a blank field and allow you
to make it a LimitToList combobox selection.

HTH,

 

[Niniel]

Ah, that is an interesting point.
I had planned on only placing entries into that table for cases when a book
does exist... but maybe I should rethink that.

 

[Armen Stein]

Ah, that is an interesting point.
I had planned on only placing entries into that table for cases when a book
does exist... but maybe I should rethink that.

We almost never have special values in a lookup table to indicate
things like Unknown. There is already a way to specify unknown - it's
a Null or a missing record.

The BookMovie junction table should have have zero rows where there is
no known relationship.

However, if it is critical that there be a positive indication that NO
books are related to this movie, then I would store another
triple-state flag value in the Movie table called RelatedBooksFlag. It
would be true if there are (see BookMovie records for known
instances), false if there are not (BookMovie should have no records
for this Movie) and null if it is unknown whether there are related
Books to this Movie (BookMovie should also have no records for this
Movie).

This kind of rigorous knowledge is rarely necessary in real
applications, however.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com