sql server - How do i update a column with specific values to reference the primary key ID of another table?

Question

My goal is to normalize my table. My table before normalization is called Product Specifications, to normalize it I have two other tables called buyers_table and product_table

Table "Product Specifications"

Columns: Product_id , model_number, buyer

Buyers_table

Column: Buyer_id, buyer

Product_table

product_id, model_number, buyer_id

How do I insert the buyers from my product_specification table into my product_table but have the values show up as the buyer_id value instead of buyer?

Sorry I am new to this. This is a simplified version of the database that I have set up for my company.

question from:https://stackoverflow.com/questions/66066589/how-do-i-update-a-column-with-specific-values-to-reference-the-primary-key-id-of

Answer 1

use this design for database

Product_buyer: Columns:Id, Product_id , Buyer_id

Buyers_table: Column: Id, Buyername

Product_table: Id, Model_number

Product_Specifications: Id,Product_id,Productname,Price

then For insert data to database

1. First enter the information in the Product_table table
2. Retrieve the latest Product_table table record and enter Id as Product_id and the other of the information in the Product_Specifications table.
3. Enter the buyer information in the Buyers_table table when purchasing
4. Product_buyer

Create tables

create table Product_table
  (
    Id int identity,
    Model_number int,
    primary key(Id));


    create table Product_Specifications
    (Id int identity,
     Product_id int,
     Productname nvarchar(150),
     Price int,
     primary key(ID),
     foreign key(Product_id) references Product_table);

   //**********************
     //and define other table
   //......................

Retrieve product information

select Product_table.Id,Product_table.Model_number,Product_Specifications.Productname,Productname.Price
from Product_table join Product_Specifications
on Product_table.Id = Product_Specifications.Product_id

Retrieve Product_buyer information

select Product_table.Id,Product_table.Model_number,Product_Specifications.Productname,Productname.Price,Buyers_table.Id,Buyers_table.Buyername
from Buyers_table join Product_buyer on Buyers_table.Id = Product_buyer.Buyer_id
join Product_table on Product_buyer.Product_id = Product_table.Id
join Product_Specifications on Product_table.Id = Product_Specifications.Product_id