CODEWITHSUNDEEP
mysqlmany-to-manyrelational
user8902413
user8902413

Reputation: 1

Many to many relational database

I have a table where player character information is kept. I have another table where all possible status affects are kept. I am trying to figure out how to create a relationship where any/all/none of the character entries in the first table can be linked to any/all/none of the status entries in the second table. My research has shown me that I will probably need a third table, and may have to use composite Primary Keys and/or surrogate keys?

So here is an update based on responses. I seem to have working what I was hoping to achieve. Here is the code:

    create table characters (
    char_id int primary key auto_increment,
    name varchar(25)
    );

    create table health_status (
    status_id int primary key auto_increment,
    stat_name varchar(15)
    );

    create table char_status (
    char_id int,
    status_id int,
    primary key (char_id, status_id)
    );

    insert into characters (name) values ('Godzilla');
    insert into characters (name) values ('King Kong');
    insert into characters (name) values ('Mecha-Dragon');
    insert into characters (name) values ('Chris Hanson');
    insert into characters (name) values ('Journey');
    insert into characters (name) values ('Lou Diamond Phillips');
    insert into characters (name) values ('RedHot');

    insert into health_status (stat_name) values ('Bleeding');
    insert into health_status (stat_name) values ('Shaken');
    insert into health_status (stat_name) values ('Frightened');
    insert into health_status (stat_name) values ('Petrified');
    insert into health_status (stat_name) values ('Poisoned');
    insert into health_status (stat_name) values ('Slowed');
    insert into health_status (stat_name) values ('Rushed');
    insert into health_status (stat_name) values ('Endowed');

    insert into char_status (char_id, status_id) values (1, 1);
    insert into char_status (char_id, status_id) values (1, 3);
    insert into char_status (char_id, status_id) values (1, 6);
    insert into char_status (char_id, status_id) values (2, 2);
    insert into char_status (char_id, status_id) values (2, 4);
    insert into char_status (char_id, status_id) values (3, 7);
    insert into char_status (char_id, status_id) values (6, 8);
    insert into char_status (char_id, status_id) values (7, 2);
    insert into char_status (char_id, status_id) values (7, 7);

    select characters.name, health_status.stat_name
    from characters
    left join char_status on characters.char_id = char_status.char_id
    left join health_status ON health_status.status_id = 
    char_status.status_id;

I have three questions. Does anyone see any way this could be cleaned up, or a better way to achieve my goal? Also, is having the compound PK in the table char_status really doing anything useful? And finally, Is there a way to organize the output with a query to list the character name only once, followed by all its associated status - or is this something for a different language to do? Thanks for everyone's help!

Upvotes: 0

Views: 48

Answers (2)

shushu304
shushu304

Reputation: 1498

As you found in your research

create a third table with 2 fields that will hold the "id" (main field) from those 2 tables you want to make relation many to many...

CREATE TABLE `many_to_many` ( `information_id` INT(11) NOT NULL , `status_id` INT(11) NOT NULL , PRIMARY KEY (`information_id`, `status_id`)) ENGINE = MyISAM;

Pay attension to the PRIMARY KEY that depend on thos 2 fields.

Now you can add for every information_id many status_id relations,

and for status_id many information_id relations.

For first try, run this query to add values to see it

INSERT INTO `many_to_many` (`information_id`, `status_id`) VALUES (1, 1), (1, 2), (2, 1), (2, 2);

Upvotes: 1

Chetan_Vasudevan
Chetan_Vasudevan

Reputation: 2414

Based on your structure you say you need to further create new table and put data into it.**

In order for you to connect between tableA and TableB you need to have a Primary key in one and also its reference in other

**. Many to many relationship is something like a mutual one where tableA depends on many elements of tableB and also vice-versa.

You may have to normalize your tables first based on your situation.

Have a look at Normalization_tuorial Next also have a look at one-many and many-many etc..

Upvotes: 1

Related Questions