Mysql table. Should I use multiple tables?

Question

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

nyvaken · Accepted Answer · 2012-02-26 12:51:40Z

3

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?

answered Feb 26, 2012 at 12:51

nyvaken

1201 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ryan Over a year ago

I believe so. I won't run into problem when running 'on duplicate key update' right?

nyvaken Over a year ago

As long as you don't insert a row with a model_nr & seller that is allready in the DB on one row, as that row is unique.

Ryan Over a year ago

No. the way I update the table is by creating a temp table using the same structure as the price table then updating the price table from the temp price table. Is that a good way to do it?

nyvaken Over a year ago

Why not just use (UPDATE table SET price='9' WHERE model_number='abc' and seller='Store1';), if you want to update that specific row?

Ryan Over a year ago

Well because the data is coming from an XML file and is updated every day using the approach I typed above.

|

Fabian Barney · Accepted Answer · 2012-02-26 22:57:47Z

1

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.

edited Feb 26, 2012 at 22:57

answered Feb 26, 2012 at 12:57

Fabian Barney

14.6k6 gold badges45 silver badges63 bronze badges

Comments

Zoidberg · Accepted Answer · 2012-02-26 12:52:05Z

0

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.

answered Feb 26, 2012 at 12:52

Zoidberg

10.4k2 gold badges34 silver badges53 bronze badges

Comments

conrad10781 · Accepted Answer · 2012-02-26 12:53:09Z

0

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

answered Feb 26, 2012 at 12:53

conrad10781

2,27319 silver badges17 bronze badges

2 Comments

Ryan Over a year ago

But when updating the table, will it be able to relate the new data with its corresponding auto_incremented id?

ypercubeᵀᴹ Over a year ago

Why does one need an auto-incrementing id in Products if (as the OP's requirements) a product is already identified by its model_number?

Ярослав Рахматуллин · Accepted Answer · 2012-02-26 12:57:13Z

0

   [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

answered Feb 26, 2012 at 12:57

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

7,9681 gold badge44 silver badges65 bronze badges

Collectives™ on Stack Overflow

Mysql table. Should I use multiple tables?

5 Answers 5

6 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related