Database Structure

[coopetj]

Im trying to build a database to organize comics.

I want to track the Appearance of other characters.

So.

One comic can have many different Guest Appearences. Thats a 1 to many.

A character can be a guest in many comics. That is also a 1 to many.

So I have a many 2 many relationship. That is not good.

What can I do to break this up? Create 2 tables. One to track the guest appearances and another to act as a lookup and track characters?

Im very confused.

[colin]

You could try doing something like;

character table:
character ID (Primary Key)
character name
blah!!!!!

Appearances Table:
Appearance ID (Primary Key)
Character ID (Foreign Key from character table)
comic ID (Foreign Key from comic table)
blah!!!!!!

Comic table:
comic ID (Primary Key)
comic title
blah!!!!!!

The primary keys in the character and comic tables become the foreign keys in the appearances table. This will reduce your relationships from m:m to 1:m.

Hope this helps

Colin

[honeybee]

That's the right design.

In such cases, you need to have two master or Reference tables and a relation table, which tracks the relationship between the master entities.

In the structure above, the Character and the Comic are the master or reference tables, while the Appearances table is the Relation table. Just like the Character and the Comic tables have a primary key, the primary key of the Appearances table could be said to be the Character ID + Comic ID combination. You may or may not have a generic primary key for this table like the Appearance ID.

This type of structure makes it easy to add/remove characters from comics. If you want to include a character into a comic, simply add a record to the Appearances table with the desired Comic ID and the Character ID of the character to be added. Or if you want to remove a character from a comic, locate and remove the corresponding record from the Appearances table.
.