The Programmer News Hubb
Advertisement Banner
  • Home
  • Technical Insights
  • Tricks & Tutorial
  • Contact
No Result
View All Result
  • Home
  • Technical Insights
  • Tricks & Tutorial
  • Contact
No Result
View All Result
Gourmet News Hubb
No Result
View All Result
Home Tricks & Tutorial

Review my database schema for a color picker : softwaredevelopment

admin by admin
December 30, 2022
in Tricks & Tutorial


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.



Source link

Previous Post

SD Times Open-Source Project of the Week: Open Assistant

Next Post

2023 predictions for software development

Next Post

2023 predictions for software development

Recommended

Adaptive Shield introduces new features for monitoring third party apps

2 months ago

Benefits Of Decentralized Artificial Intelligence

3 months ago

Fancy Image Decorations: Masks and Advanced Hover Effects | CSS-Tricks

3 months ago

How to Safely Share Your Email Address on a Website | CSS-Tricks

4 months ago

Rendering External API Data in WordPress Blocks on the Front End | CSS-Tricks

4 months ago

Scanbot SDK releases demo app for mobile Data Capture SDK

1 month ago

© 2022 The Programmer News Hubb All rights reserved.

Use of these names, logos, and brands does not imply endorsement unless specified. By using this site, you agree to the Privacy Policy and Terms & Conditions.

Navigate Site

  • Home
  • Technical Insights
  • Tricks & Tutorial
  • Contact

Newsletter Sign Up.

No Result
View All Result
  • Home
  • Technical Insights
  • Tricks & Tutorial
  • Contact

© 2022 The Programmer News Hubb All rights reserved.