Review my database schema for a color picker

Requirements of color picker

  1. design a system that allowed users to select their favorite colors and store them in a list
  2. the user should be able to share the favorite colors list with their friends
  3. The access control list must be defined by the owner of the favorite colors list (who can view the list)

ERD: https://imgur.com/AJJ7hsJ

Entities:

  1. colors (enum table)
  2. user table
  3. favorite_list is a join table between colors and the users
  4. friends and permissions tables are join tables for the access control list. They are more or less the same table

Some common SQL:

Fetch the favorite colors of a particular user

SELECT u. email, c. name

FROM favorite_list AS fl

INNER JOIN colors AS c

ON c. id = fl. color_id

INNER JOIN users AS u

ON fl. user_id = u. id

Add a new color to the favorite list of a specific user

INSERT INTO favorite_list

color_id, user_id

VALUES (42, 53);

Fetch the list of users who can view the favorite list of a particular user

SELECT user_id

FROM permission

WHERE owner_id = 42;

Remove a particular color from the favorite list for a specific user

DELETE FROM favorite_list

WHERE color_id = 5 AND user_id = 42;

Please share your feedback! I might have a wrong SQL query or overseen something in the database schema.

submitted by /u/sdxyz42
[link] [comments]

from Software Development – methodologies, techniques, and tools. Covering Agile, RUP, Waterfall + more! https://ift.tt/Tupn46b

Leave a comment

Design a site like this with WordPress.com
Get started
search previous next tag category expand menu location phone mail time cart zoom edit close