Mysql – Requesting help with setting up design of a few tables and the correct foreign keys

MySQL

needing some help setting up some tables and their foreign key constraints.

3 total tables:

UserTable – contains:

  • UserID
  • Other Fields

ShapeTable – contains:

  • ShapeID
  • UserID (from UserTable)
  • Other Fields

RecipientTable – contains:

  • RecipientID
  • UserID (from UserTable)

A user is able to draw a shape, or many shapes. A user can also add potentially many recipients. A shape can have 0 or many user's recipients added to it.

If a recipient is deleted by user, it should be removed from all stored shapes. If a shape is deleted by user, it should not delete recipients.

I'm struggling with how to design the "mappings" between the Shapes and the Recipients for each user.

When a user draws a shape, I want to present all the recipients for the user that drew the shape. The user will then be able to select 0, 1, or many of those recipients for that given shape. I'll need to be able to retrieve this later on as well. When a shape is deleted, I want it to delete the "mappings" to any recipients as well, but not necessarily delete the actual recipient record.

What is the best way to arrange the tables, should there be an additional "mappings" type table created, and what foreign key constraints should exist to enforce cascading and referential integrity?

Let me know if there's a better way to present the question if I left something out. I am using MySQL (InnoDB) and PHP (if that matters).

Best Answer

With a many-to-many relationship (each shape can be associated with many recipients, and each recipient can be associated with many shapes).

Here's the create statements for the mapping table (plus my dummy definitions for the three tables you've already got):

CREATE TABLE `user` (UserID int AUTO_INCREMENT NOT NULL,
                     userVal varchar(32),
                     CONSTRAINT pk_user
                     PRIMARY KEY (UserID)
                    );

CREATE TABLE `shape` (ShapeID int AUTO_INCREMENT NOT NULL,
                      UserID int,
                      shapeVal varchar(32), 
                      CONSTRAINT pk_shape
                      PRIMARY KEY (ShapeID),
                      CONSTRAINT fk_shape__user 
                      FOREIGN KEY (UserID) 
                      REFERENCES `user` (UserID)
                     );


CREATE TABLE `recipient` (RecipientID int AUTO_INCREMENT NOT NULL,
                          UserID int,
                          recipientVal varchar(32),
                          CONSTRAINT pk_recipient
                          PRIMARY KEY (RecipientID),
                          CONSTRAINT fk_recipient__user
                          FOREIGN KEY (UserID)
                          REFERENCES `user` (UserID)
                         );

CREATE TABLE map_shape_recipient
       (ShapeID int,
        RecipientID int,
        CONSTRAINT pk_map_shape_recipient
        PRIMARY KEY (ShapeID, RecipientID),
        CONSTRAINT fk_map_shape_recipient__shape
        FOREIGN KEY (ShapeID)
        REFERENCES shape (ShapeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT fk_map_shape_recipient__recipient
        FOREIGN KEY (RecipientID)
        REFERENCES recipient (RecipientID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
       );

You'd probably want the same ON DELETE and ON UPDATE for the shape and recipient foreign keys, but you didn't specify, so I left that out.

Tested via db-fiddle; that has the four CREATE TABLE statements, some sample data, and shows what happens in the mapping table when a shape or a recipient is removed.

NOTE: Presumably, a recipient and a shape should only be associated if they belong to the same user.

You might be able to make the primary keys of shape and recipient the unique ID from the table combined with the UserID. map_shape_recipient would require the UserID column too, and (if there's only one and it's part of the foreign keys to shape and to recipient) they'd have to have the same UserID.

Technically at least, this would be a denormalization (the map table wouldn't need the UserID because ShapeId and RecipientID both uniquely identify one row in their respective tables).

A more solution would be an INSERT trigger on the map table, that prevents a row from being inserted unless the UserID from the shape row and the UserID from the recipient row match.

In your case, your application should prevent associating a shape with a recipient that doesn't have the same UserID. I would still recommend some mechanism to ensure relational integrity, in case someone directly interacts with the database outside of the application.

Related Question