CODEWITHSUNDEEP
sqlpostgresql
personwholikestocode
personwholikestocode

Reputation: 561

How to add a update a column to add foreign keys in SQL

I am creating an Ebay clone project where users can bid on multiple products. When the auction time is up on a project, the user with the highest bid wins the product. Here are my current SQL tables I plan to use:

enter image description here

How would I create an SQL update command to add a product id to a users products_won column? This is the way I have now, but I doubt it would work:

enter image description here

I understand many to many and one to many relationships, but this seems like a one to many relationship. Since users can win multiple products, however products can only have one highest bidder? Because it seems like a one to many relationship, I don't believe I need to create a joining table. Appreciate any help

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You want a "bridging" table, user_products:

create table user_products (
    email text foreign key references users(email),
    product_id int foreign key references products(id)
);

Then to assign a product to a user, you simply insert into this table.

Having said that, you really want an auto-incremented integer id in the users table as well. Strings are slightly less efficient for foreign key references. Plus, you don't want PII used to identify records in any table that references a user.

Upvotes: 1

Related Questions