Consolidation of Data

[Ramtrap]

Hi all,

I have been racking my brain trying to reconcile this information, which my instructor was inept in explaining to me. What I'm trying to figure out is if there's an easy way for the following.

Let's say I have two tables: Movies and Categories.

Broken up for fields, let's say all I care about is the following:

Movies

1. MovieID (PRIMARY KEY)
2. MovieTitle (ex: Star Wars)

Categories

1. CategoryID (PRIMARY KEY)
2. Category (ex: Sci-Fi)

What I want to do is have a secondary table (junction table) for a many to many relationship to identify the many different categories that a movie may belong to. In the example above, let's just say I wanted Star Wars to be linked in to Sci-Fi, Action, Space, and War.

MovieCategories (this is the junction table)

1. MovieID (PRIMARY KEY)
2. CategoryID (PRIMARY KEY)

With one movie, this junction table is easy; MovieID 1 (Star Wars) would go to CategoryID 1, 2, 3, and 4 (assuming those were the only four categories, which is obviously not true). So the table would look something like this:

MovieID CategoryID
1 1
1 2


1 3
1 4

Okay, so far, so good? Now, multiply the number of MovieIDs by 10,000. How can I most easily give appropriate categories to movie titles without having to do so by arbitrary ID numbers? How do I best build that table and still keep everything normalized? Does this make any sense?

I've attached a basic outline of what I'm dealing with, along with a pre-completed Junction Table (ID'd as: tblAMoviesCategories). If that table were delete, is there an easy way to reattribute those categories to the movie titles without having to do so at the primary key level? I have a project that has nothing to do with movies, but I'm trying to see if there's a better way to come up with a solution because I'm building a database from scratch and it almost seems easier to build either one large table with all this stuff in there (i.e. not normalized at all) or a slew of excel spreadsheets I can import and delete columns as necessary).

Any help would be much appreciated. Thank you!

[ranman256]

DONT use CataID.
The catagory table has unique names, use them as the key. (not numbers)

tCata
------
scifi
action
romance

Unlike People ,who cannot be index on a unique identifier, some things can. (state codes, country codes, movie cataories)
so just use plain text as your key!

[orange]

ramtrap,

Here is an article describing the design of a junction table to accommodate a many to many relationship.

Here is a link to wikipedia article on associative entity-- also referred to as
linking table, bridge table, bridging table, junction table, mapping table and many more....

Here is another source with an example of junction table and Many to Many relationship.

Good luck.

Also: I can not open your zip ---get an error.