Requirements of color picker
-
design a system that allowed users to select their favorite colors and store them in a list
-
the user should be able to share the favorite colors list with their friends
-
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:
-
colors (enum table)
-
user table
-
favorite_list is a join table between colors and the users
-
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.