CODEWITHSUNDEEP
mysqldatabase-design
Ryan
Ryan

Reputation: 14659

Mysql table. Should I use multiple tables?

I'm implementing a price database where multiple sellers sell the same product at different prices. Each product is unique, and is identified by its model_number. Right now I have 1 seller in my price table. I am looking for the best way to build a table that can list multiple prices with each price being a field for a row whose primary key is model_number

The table looks like this:

model_number | seller | price 
abc            Store1   99.99

This structure works well when there is only 1 seller, as the primary key is model_number and my query to update the price uses on duplicate key update, which only updates the product if the price changes. Furthermore, the primary key is the model_number, but if I have multiple sellers that have the same model_number but with a different price, I believe when updating the table, the database will get confused as there will be duplicates of model_number.

Should I give each seller a unique key for their product? for instance, should seller_1 have a primary key of seller_1 and keep that value the same so the database knows to update if the price field of seller_1 changes.

Thanks

Upvotes: 0

Views: 939

Answers (5)

Fabian Barney
Fabian Barney

Reputation: 14559

enter image description here

Three tables: Seller, Seller_Product, Product

Each row of the linking table Seller_Product contains two Foreign Keys to corresponding Seller and Product row. The price is an attribute of the linking between a Seller and a Product. So it becomes a field in the linking table.

Seller: ID, Name, ...
Seller_Product: Seller_ID, Product_ID, price, availability, ...
Product: ID, Name, model_number, manufacturer, ...

This is a common table design for N:M Relationship.

Upvotes: 1

Ярослав Рахматуллин
Ярослав Рахматуллин

Reputation: 7921

   [seller]   [product_seller]  [product]
    -id        seller.id         -id -price
               product.id

if you need products to be unique, you'll need another table:

[seller]  [product_seller] [product]  [seller_price]
-id        seller.id        -id        product.id
           product.id                  seller.id
                                       -price

Upvotes: 0

conrad10781
conrad10781

Reputation: 2283

Many to many relationships are what you are dealing with here.

You want three tables for this solution:

Products: A table with products information, including a unique auto-incrementing id.

Sellers: A table with sellers information, including a unique auto-incrementing id.

Products_To_Sellers: A table with product_id, and seller_id ( INDEX these columns individually )

With this approach, you can JOIN the tables to view sellers products, but you ultimately have a SCHEMA that scales infinitely

Upvotes: 0

Zoidberg
Zoidberg

Reputation: 10333

So far your table structure looks good, all you would need to do is add the seller column as part of the primary key of the table. What you have here is a Many-to-Many relationship between products and sellers. That many to many relationship has some additional data, which is the price.

I think what you mean by "give each seller a unique key for their product" is actually what they call a surrogate key. You could give the table a primary key column so it looks like this

id  |  model_number  |   seller  |   price

Where id is the primary key of the table, and model_number and seller is are secondary keys and foreign keys, but that is really up to you, either way will work just fine.

Upvotes: 0

nyvaken
nyvaken

Reputation: 120

You can make the model_number and the seller primary key. That way you can have several tuples with the same model_number as long as the sellers differ.

model_number | seller | price 
abc            Store1   99.99
abc            Store2    9.99

Is this what you want?

Upvotes: 3

Related Questions