sql - Database Design for Order management for multiple sources -

i know what's best method building order management database there multiple shops, each own list of products , prices.

my current design

product ------ name image price_per_item   shop ------ name product_list - manytomany field    order ------ order_id    primary key shop    order content ------ order_id    foreign key order product quantity 

the problem foresee each shop may have same item may sell item @ different price_per_item.

how build db shops can manage have different price per items , yet have multiple items sell?

you're worried relationships, , not worried enough tables.

first, let's build shop table.

shop ---- shop id shop number shop name shop address ... 

generally, tables have singular name , auto-incrementing integer primary key. called blind key. that's why have shop id , shop number. shop id primary key , shop number data. shop number can changed without changing of relationships we're going build.

now, let's build product table.

product ------- product id product name product manufacturer id ... 

the product id primary key. product manufacturer id foreign key product manufacturer table. i'll leave creation of product manufacturer table exercise you.

now, shop can have many products, , product can carried in many shops. many-to-many relationship. model relationship junction table.

shopproduct ----------- shop id product id product cost product price product count ... 

the primary key (shop id, product id). have unique index on (product id, shop id). product price , product count values belong many-to-many relationship, not shop or product.

you can list of products shop through primary key. can list of shops product, , total inventory average price through unique index.

now, can talk orders. have problem order can go 1 manufacturer. want combine orders stores can order correct quantities stores.

let's build order table.

order ----- order id product manufacturer id order time stamp order delivery date ... 

the order id primary key. product manufacturer id foreign key.

next, let's build orderitem table. standard name items in order.

orderitem --------- orderitem id order id store id product id ... 

the orderitem id primary key. order id foreign key. store id foreign key. product id foreign key.

you manufacturer information product manufacturer id in order table, using order id.

you order information order id

you store information store id. information not necessary order. necessary know deliver products after manufacturer ships products.

you product information product id.

i hope enough of explanation started. first, model objects, store , product. then, model relationships between objects, shopproduct.