CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designmany-to-many
John
John

Reputation: 49

SQL many to many relationship table

I'm trying to make a many-to-many relationship between these two tables. 

CREATE TABLE series (
    title VARCHAR(30),
    language VARCHAR(30),
    year INT,
    PRIMARY KEY (title)
);

CREATE TABLE actors (
    name VARCHAR(30),
    age INT,
    country VARCHAR(30),
    serie VARCHAR(30),
    PRIMARY KEY (name , age , country , serie),
    FOREIGN KEY (serie)
        REFERENCES series (title)
);

I tried to create a separate table to form a many-to-many relation. Does this look correct?

CREATE TABLE series_actors_combined (
    title VARCHAR(30),
    name VARCHAR(30),
    age INT,
    country VARCHAR(30),
    serie VARCHAR(30),
    FOREIGN KEY (title)
        REFERENCES series (title),
    FOREIGN KEY (name)
        REFERENCES actors (name),
    FOREIGN KEY (age)
        REFERENCES actors (age),
    FOREIGN KEY (country)
        REFERENCES actors (country),
    FOREIGN KEY (serie)
        REFERENCES actors (serie)
   );

Upvotes: 0

Views: 1254

Answers (1)

GMB
GMB

Reputation: 222682

Your tables do not look right.

As a starter, you have a foreign key in the actors table that references the series, which basically defeats the purpose of the bridge table.

Also, the foreign keys from the bridge table to actors are not well-formed: you have one key per column, while you should have a unique, multi-column key to reference the actor. I would recomend having auto incremented primary keys rather than using these combinations of columns. This gives more flexibility to your design (in real life, two different series might have the same title), and makes it much easier to create foreign keys in the bridge table.

Consider:

create table series(
    id int primary key auto_increment,
    title varchar(30) unique,
    language varchar(30), 
    year int
);

create table actors(
    id int primary key auto_increment,
    name varchar(30), 
    dob date,     -- better than age 
    country varchar(30)
);

create table series_actors(
    series_id int references series(id),
    actor_id int references actors(id),
    primary key (series_id, actor_id)
)

Upvotes: 4

Related Questions