How to insert multiple values in single column in MySQL

Question

I've created two simple tables in phpmyadmin. A list of "content creators" and a list of "content creator types". Each "content creator" can have multiple "content creator types" associated with them so this is a one-many relationship. In phpmyadmin I created the following relationship.

ALTER TABLE `contentcreator` ADD FOREIGN KEY ( `ContCreat_TypeID` ) REFERENCES `MyDatabase`.`contentcreator_types` (`ContCreat_TypeID`) ON DELETE RESTRICT ON UPDATE RESTRICT ;

Now when I try to insert data (using the insert data interface in phpmyadmin) into the table "content creator" - I get a drop down to allow me to choose one of "content creator type IDs". However I want to be able to add multiple "content creator type IDs" to each content creator. How do I do this within phpmyadmin using the insert data interface?

Are you sure that this is not a many to many relationship? A creator can have many types, and a type can be on many creators — Stefan H
– Stefan H, Commented Jul 13, 2012 at 16:32
It's actually a Many-to-Many relationship. You'll need a third table that has FK references to both your content creators table's PK and your content creator types table's PK. This is often called a Junction or a Join table. — Brian Driscoll
– Brian Driscoll, Commented Jul 13, 2012 at 16:32
Thanks for pointing out my mistake. I will create a junction table as suggested. — Ansh
– Ansh, Commented Jul 13, 2012 at 16:43

Stefan H · Accepted Answer · 2012-07-13 16:41:21Z

It sounds like you have a many to many relationship here, not a one to many. You need to add an intersection table with foreign keys to both your types table, and your content creators table.

If you wanted to do this as a one to many relationship, the side that is many gets the foreign key, not the side that is one. Meaning you would have a foreign key to the content Creators table on the content creators type table. Then each content creator would get a new entry in the type table for each type that it had, however you would have duplication in that scenario.

Many TO Many:

ContentCreatorID CreatorName ---------------- ------------------ 1 Creator1 2 Creator2 3 Creator3 CreatorTypes (intersection Table) ContentCreatorID TypeID ---------------- ------------------ 1 1 1 2 2 3 3 2 Types TypeID TypeName ---------------- ------------------ 1 Type1 2 Type2 3 Type3

Thanks for the tips! I will add the intersection table as suggested to connect the two.

Decker W Brower · Accepted Answer · 2012-07-13 16:40:59Z

You may want to consider creating a mapping table to associate creators with their types

 creator table id | creator type table id | type mapping table creator_id | type_id

Collectives™ on Stack Overflow

How to insert multiple values in single column in MySQL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related