DB Table Design in MS Access Question (Sub DB)

[BMillikan]

Is there a way to create an "array" of items within a MS Access DB Field?
For example, I'm making a movie DB and I want one field for Actor/Actresses.
I have a separate DB with Actor/Actress Names and it's automatically
enumerated and each actor or actress is assigned a number. Basically, I want
to allow the Actor(s)/Actress(es) field in the main DB contain more than one
Actor/Actress "ID". Is there a way to do that?

 

[Nikos Yannacopoulos]

That's not the way to go! The relationship between movies and actors is
a classic many-to-many one, so what you need is another table with one
field for MovieID and one for ActorID (foreign keys), which will have
one record for each actor in a movie - as well as one record for each
movie each actor has been in. You could call it tblCast, I suppose?

HTH,
Nikos

 

[BMillikan]

Thanks for the input. I'm relatively new to "Database Engineering". I"m a
Visual C++ software design by trade and work mostly in embedded software
applications and Visual C++ Dialog-based control program to "talk" to the
embedded system. So, I'm very much a "newbie". Can you explain a bit
further the many-to-many philosophy because there are other multiple search
criteria that I want for each movie.... like Genre (the move may fit into
more than one genre), Content (language, violence, sexuality... etc),
Actor/Actresses, etc. So, it seems I need several tables to describe each
movie. So, what is your suggestion. Is there a template in Access that I
might be able to use an an example? Eventually, I'm going to "port" this DB
to a server (MySQL or ProgreSQL) for maintenance and SQL searches. I want to
be able to search or filter selection based on rating, content, actor, etc.

Thanks,
Brian

 

[tina]

suggest you read up on table normalization/relationships. see the following,
from a newsgroup post of March 13, 2005:

********
See the following link for more information than you could
possibly imagine:

http://www.ltcomputerdesigns.c­om/JCReferences.html


There are sections on Normalization and Book Recommendations.
--
Jeff Conrad
Access Junkie
Bend, Oregon

********

hth