MySQL Database Design Suggestions

Question

I have the following fields in my table:

id - user_id - display_time - icon_id - background_id - page_name - template_id - sort_id - time

Basically, each user has their own slides that they can create and these are just some settings within it. These are for the custom slides but each user will have 12 or so default slides that are pre-made.

My question is.. for the pre-made slides that all users get, I don't need to define all of those fields. It will already have a background and icon and all of that, that cannot be changed. However it needs to be on this table because the user can still change the sort order and the time.

So... I could define the background and icon for each pre-made slide, but in the future if I did want to change the background of the universal "Dashboard" page then there is no easy way to make the change, since it is already in the database for that background_id.

Sorry if that was really confusing. Does anyone have any suggestions on how to approach this? I hope I have the right idea here.

---

So this table is good for the custom slides but I need to fit the universal pages that all users get in here so they can define some important variables like how long the slide will show for and the order. Is there an easier way to do this?

Edit:

I need to have the ability to add more than 1 of the same slide in the sort order. So if someone wants slide 1 to show up again and just change the sort order and display time. So they can have many different "instances" of each slide.

Can I take out the slide_id PRIMARY key without it messing anything up? It says it is Unique right now which I think is making this mess up if I try to add another of the same slide.

Thanks!

Answer 1

This is similar to the answer given by @Freeman Latif. Showing the table definitions may make it more clear where the sort and display attributes belong: these attributes belong in the table that associates users to their slide choices.

I'm making some guesses about how you flexible you want these attributes to be. So my apologies if you have different intentions for the attributes than my design supports.

For instance, I'm adding a Templates table so that you can define a default icon & background and then apply that design as a default to the slides. Then if you ever change the default background, you only need to change it in one row in the Templates table instead of many rows in the Slides table. That's part of the benefit of normalization: a fact like the background is only recorded in just one place, so you don't accidentally change it on some rows and not other rows.

CREATE TABLE Templates (
    template_id      INT PRIMARY KEY,
    icon_id          INT,
    background_id    INT
);

CREATE TABLE Slides (
  slide_id           INT PRIMARY KEY,
  template_id        INT,
  page_name          VARCHAR(20),
  FOREIGN KEY (template_id) REFERENCES SlideTemplates(template_id)
);

CREATE TABLE SlideCustom (
  slide_id           INT PRIMARY KEY,
  icon_id            INT,
  background_id      INT,
  FOREIGN KEY (slide_id) REFERENCES Slides(slide_id)
);

Note that slide_id is both a foreign key referencing Slides, and also the primary key of SlideCustom. So it has a 1:1 relationship to that table.

A given user's slide choices include the choice of slide, whether it is a default slide or a custom slide, and the user's choice for sort order and display time. Therefore multiple users can choose a given slide, but each user can have a different choice for sort and display of that slide.

CREATE TABLE UserSlides (
  user_id            INT NOT NULL,
  slide_id           INT NOT NULL,
  sort_order         INT NOT NULL,
  display_tenths_sec INT NOT NULL DEFAULT 10,
  PRIMARY KEY (user_id, slide_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (slide_id) REFERENCES Slides(slide_id)
);

Now you can get every slide for a user and the slide's icon and background, whether it's default or custom:

SELECT u.user_id, u.display_tenth_sec,
  COALESCE(c.icon_id, t.icon_id) AS icon_id,
  COALESCE(c.background_id, t.background_id) AS background_id,
FROM UserSlides u
JOIN Slides s USING (slide_id)
LEFT OUTER JOIN SlideCustom c USING (slide_id)
LEFT OUTER JOIN Templates t USING (template_id)
WHERE u.user_id = 20860
ORDER BY u.sort_order;

---

Re your comment and additional requirement that a user can use a given slide more than once:

Certainly the sort order is unique for a given user. So you can make (user_id, sort_order) be the primary key, and slide_id is just another non-unique attribute:

CREATE TABLE UserSlides (
  user_id            INT NOT NULL,
  slide_id           INT NOT NULL,
  sort_order         INT NOT NULL,
  display_tenths_sec INT NOT NULL DEFAULT 10,
  PRIMARY KEY (user_id, sort_order),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (slide_id) REFERENCES Slides(slide_id)
);