design - How to differentiate a many-to-many relationships while designing a database? -

i have design database. , finding entities , relationships. every relationships seems have many-to-many relationships. instance, in case:

1) staff manages client

here staff can manage 0 or more client. similarly, client managed 1 or more client.

2) client orders buy stock

here client can order zero, 1 or more stock buy , stock can ordered zero, 1 or more client.

3) client orders sell stock

here client can order zero, 1 or more stock sell , stock can ordered zero, 1 or more client sell.

these of examples of situation. , confused how separate these relationships. there other numerous cases these in scenario. , having difficulty conceptualize design.

so, please enlighten me regarding situation.

it seems there's quite lot system developing , presumably there requirements haven't mentioned isn't possible come complete answer. of following "conceptualize design" describe it.

1) common scenario , there's pretty standard way of dealing these many-to-many relationships.

if there 2 entities , b many-to-many relationship introduce entity c consists of 2 columns - 1 foreign key unique id of , other foreign key unique id of b. , remove foreign key column in entity pointing b , vice versa.

i.e

|-----|  |   |   |-----|    \|/    |    |   /|\ |-----|  |  b  |   |-----|  

becomes:

|-----| |-----| |   | |  b  | |-----| |-----|    |       |    |       |    |       |   /|\     /|\ |-------------| |      c      | |-------------| 

the main challenge call these new entities! might a_b_relationship it's if can identify more meaningful names.

2) looks need bit more analysis identify actual entities. 1 way of doing go through description of system , identify nouns - if there's noun in description it's appropriate have entity in entity-relationship diagram.

"order" jumps out noun overlooked.

typically order-processing have 2 entities - order contains date, total value, customer etc, , child orderline identifies how many of product have been ordered , individual prices. in ecommerce shopping cart order , each item in shopping cart orderline record.

in scenario we'd have:

|----------|            |-----------| |  client  |            |  product  | |----------|            |-----------|       |                       |       |                       |       |                       |      /|\                     /|\    |-------------|       /|-------------|  |    order    |--------|  orderline  | |-------------|       \|-------------|  

3) client sells many products

here identifying additional role client , i'd here question whether "client" appropriate entity @ stage. may find easier think in terms of "buyer" , "seller" until first-cut design understood. if buyer , seller have lot in common (especially if individual can both buyer , seller) may decide use single entity eventually. erd tool may provide support - have search "subtype entities" or "entity subtypes".

the specifics depend on actual application each orderline should have relationship seller, , order relationship buyer. depend on whether possible example buyer order number of items of particular product, of sourced 1 seller , another. complicated!

also, might helpful consider whether need record seller's stock prior being sold. here might useful distinguish between "product" , "stock", e.g.

|---------| |-----------| |  seller | |  product  | |---------| |-----------|    |           |    |           |    |           |   /|\         /|\ |-----------------| |      stock      | |-----------------| 

as general comment i'd can go through design process step step. once have got initial model, assign data items need store appropriate entity, , methodically make sure design in first normal form, second normal form 3rd normal form. once have done this, , confident design reflects requirements, should think how implement design in database. that's learned many years ago anyway!