Extra table for that?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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)
 
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. :)

Steve said:
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 said:
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.
Click to expand...
Click to expand...
 
I'm trying to update this movie database.

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.
Click to expand...

I'd use *two* extra tables (at least).

One table would model movies based upon eponymous books. This would
require only the movie's identifier -- no need to redundantly store
the eponymous title! Note the relationship type is 1:0..1.

Another table would model the relationship between a movie and its
books having disparate titles (non-eponymous). This would require
identifiers for both movie and book (which could be the book's title,
of course). The relationship type would be 1:m (perhaps 1:0..1 if you
wish to simplify and record just the book on which the book is
*primarily* based).

You would need to ensure that for the second table the title of the
movie is not the same as the title of the book (or a constraint to
ensure a movie cannot appear in both tables, if you are using the
aforementioned 'simplified' approach).

For completeness, I'd probably use a third table to model movies not
based on a book and ensure *all* movies appear in exactly one of the
three tables.

If I haven't guessed correctly, please post some more details e.g.
relationship type between movies and books in your model.

Jamie.

--
 
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.
 
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,
 
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.
 
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.
Click to expand...

I sometime use that approach for sub atomic attributes i.e. columns of
the same row. Say you have columns for first_name, middle_name and
last_name: a NULL middle name isn't appropriate and using '{{NK}}' and
'{{NA}}' seems a good solution.

But you seem to be proposing having *entities* (i.e. the referenced
table is not a lookup table) with the non-existent book titles
'(Unknown)' and '(none)'. I can't put my finger on a specific flaw but
using the 'book title' attribute to indicate the relationship type
doesn't feel right.

Also it seems to me you are mixing entities and relationships within
the same table. Why do you propose a field in Movies table to model a
relationship between movies and book titles (note 'book titles' as
distinct from 'books')? Why not a field in the book titles table? Why
not both?! The answer to my 'why' questions is that the correct way to
model a relationship between entities is to use a dedicated table.

Jamie.

--
 
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.
Click to expand...

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
 
Back
Top