Setting up multiple entry fields

R

Rick Teaford

I have been away from database programming for a little while and cannot find
the right terminalogy for this. I have to setup a table that will have
varible number of entries.
ie: I want to group my movie together by actor they have in them. Sometime
I will have 6 or 7 and sometime I will have 2 dozen. I want to be able to
search all field when I am searching for a match.
Could you tell me what this is called (grouping, subcatigatizing, etc.)?
Also could you tell me where I could find some good referance book that are
more techinal in nature (not basic)?
I would appreciate any suggestions people could give me.
 
P

Pat Hartman \(MVP\)

The relationship between actors and movies is many-to-many. That means that
you need a relation table to implement the relationship. Add a table with
ActorID and MovieID. You may also want to add a field called Role where you
can store the name of the character the actor played in that movie. Of
course, if you want to handle movies like "Coming to America" where several
actors played several roles, you will need yet another relation table.

Don't make the mistake of trying to stuff a bunch of actor names into a
single field. You will have trouble searching on that field.
 
J

John W. Vinson

I have been away from database programming for a little while and cannot find
the right terminalogy for this. I have to setup a table that will have
varible number of entries.
Click to expand...

Then it's not a table.
ie: I want to group my movie together by actor they have in them. Sometime
I will have 6 or 7 and sometime I will have 2 dozen. I want to be able to
search all field when I am searching for a match.
Could you tell me what this is called (grouping, subcatigatizing, etc.)?
Click to expand...

It's called a Many to Many Relationship, and uses three tables:

Movies
MovieID
Title
DateReleased
... <other info about the movie as an entity, nothing about actors>

Actors
ActorID
LastName
FirstName
<other biographical info as needed>

Cast
MovieID <link to Movies>
ActorID <link to Actors>
Role said:
Also could you tell me where I could find some good referance book that are
more techinal in nature (not basic)?
Click to expand...

Designing Relational Database Systems by Rebecca Riordan, if you're lucky
enough to find a copy.

Other references in

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]
 
J

John W. Vinson

On Mon, 23 Apr 2007 19:38:57 -0400, "Pat Hartman \(MVP\)" <please no
The relationship between actors and movies is many-to-many. That means that
you need a relation table to implement the relationship. Add a table with
ActorID and MovieID. You may also want to add a field called Role where you
can store the name of the character the actor played in that movie. Of
course, if you want to handle movies like "Coming to America" where several
actors played several roles, you will need yet another relation table.
Click to expand...
Good point. Or you could have the same ActorID multiple times, making MovieID,
ActorID and Role a joint three-field primary key.

John W. Vinson [MVP]
 
T

Todos Menos [MSFT]

of course.. with MS Access you can't even enforce RI in a many to
one.. so it's kinda ridiculous to use MDB for a many to many

I mean seriuosly-- with linked tables-- you can't enforce RI _AT_ALL_
right?

what a stupid friggin database

Access _ROCKS_ but MDB sucks balls

ADP uber alles
 
Top