-2

Problem:

I'm creating a program that gets the price of a product on a website stores information on that product, the user adds this product to an alert list where when the price changes the user is sent a notification.

I'm currently designing a database to store this info.

I've got these tables: user, list, product

I'm trying to workout what fields to store in my list table, each list is unique and can hold multiple products within the 1 unique list, I'm trying to wrap my head around how I store multiple products with multiple unique ids within the one list so that when a list is queried it returns the products the user has in that list, I've been researching best practices on this but I'm missing something and hoping this can be explained to me for my situation

I've currently got the following for each table

user

ID
username
email
password
created_on
created_by

list

list_name
product_ids(there will be many of these within one list)
user_id
created_on
list_id

product

product_id
product_name
product_price
last_updated
created_on
stock_status
product_url
JDoug98
  • 1
  • 1
  • 1
    Wikipedia has a helpful article, [database normalization](https://en.wikipedia.org/wiki/Database_normalization). A many to many relationship requires a [junction table](https://en.wikipedia.org/wiki/Associative_entity). – Gilbert Le Blanc Aug 16 '23 at 22:51
  • 2
    Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Aug 16 '23 at 23:22
  • Must you have more than one list per user? Doesn't one list per user suffice? And don't you want to store a threshold price with each product in a list? – Thorsten Kettner Aug 17 '23 at 00:00
  • Thanks for the info @GilbertLeBlanc, I'm just struggling to picture how this works in my situation, I'd have to have another table with like ListItems which has a list_id and a product_id so that would give the results of each item stored within a certain list? – JDoug98 Aug 19 '23 at 13:45
  • If I understand your question correctly, each user has one and only one list. Each list can contain many products. A one to one relationship requires a foreign key. A one to many relationship requires a compound key or index, one part pointing to the list and the other part the unique product number within the list. Basically, what you outlined in your question. Now, if my assumptions about the relationships are wrong, then you need a different relational structure. Relational tables are all about relationships. – Gilbert Le Blanc Aug 19 '23 at 17:47
  • @GilbertLeBlanc Okay, for expansion purposes I'd like each user to be able to store many lists but each list will only have 1 user. A list will have many products and each product will be a part of many lists – JDoug98 Aug 19 '23 at 18:37
  • Okay, then your list requires a compound key or index, one part pointing to the user and the other part the unique list number for the user. Go back to the Wikipedia normalization article and learn the concepts, so we don't have to keep explaining them over and over again. – Gilbert Le Blanc Aug 19 '23 at 22:18

1 Answers1

0

You should not have product ids in the list table, you should create an intersection table that’s sits between product and list and has FKs to both of them

NickW
  • 8,430
  • 2
  • 6
  • 19