How to translate high level entity relationship into schema?

Can new entity of many-to-many relationship generate new entity?

• Relationship between Item entity and Supplier entity is many-to-many, then this relation generates a new entity called Purchasing. Then the relationship between Purchasing entity (new ...show more

• Answer:

  I don't know what people are learning these days, but a many-to-many relationship used to be considered nearly worthless in a database/application design. The relationship between two entities almost always carries extra information so that an intermediate entity can be defined that is one-to-many with both side of the original many-to-many relationship. A line item on a purchase order, a where-used item in a bill-of-materials database, and enrollment entry in a student information system are all examples. In your case, the intermediate might be an "approved supplier" entity that connects each item in inventory to a single supplier. If nothing else, this design removes some dual maintenance of a list of items in each supplier entity and a list of items in each supplier entity. Adding an item never creates a supplier and adding a supplier never creates an item. Instead, when both exist, you can add an approved supplier entity that says you may order a particular entity from a particular supplier. Extra information that the approved supplier entity/object/record/tuple might carry is "who authorized this vendor for this item, and when" fields. Maybe there are effective dates or status information as well so that reporting on past orders can be done without allowing new purchases from a vendor who has discontinued a particular product or gone out of business.