How can I be sure that many to many relationships are created right when I click on one table and another table?

Hello Everyone,

I have basically 13 different tables. I have Customer table, a Sale table which is connected to Customer table because a customer can buy a Sell and that same Sale table is also connected to an Employee table because an Employee can Sell a Sale. Then I have an Order table connected to a Vendor table because a single order can be placed to a Vendor just as well as many different orders can be placed to many different vendors. But also the Employee table can be connected to a Shop table because an Employee can work at a single shop just as well as many employees work for different shops. The Employee and Shop table is connected by EmployeeShop which have reference or have foreign keys to both the Employee and Shop table. The rest of the relationships can be shown in the pic below. My problem is that I am not entirely sure that some of my tables that are connected to some of the other tables, for example, Ingredient table that is connected to the OrderLineItem, is entirely right. And also Order table and Vendor which are connected by the OrderVendor. Please any help or advice would be beneficial to actually moving on and forward my model to an physical database.

I’ve read your post several times but I am not 100% sure of what your question is.

I understand that a many-to-many relationship between Order and Vendor is usefull if more than one vendor an be related to the one order and, vice-versa, many orders can be related to just one vendor.

If only one vendor can be related to one order and no other vendor can be related to that one order, then you wouldn’t need a many-to-many relationship. A one-to-many relationship would be enough, and you could drop the OrderVendor table.

If that is possible, then your schema is correct, at least for the vendor part.

The rest is a bit obscure to me but the connection between customer - sale and employee looks fine.

With employee and shop, I have the same issue. If an employee can belong to more than one shop at the same time, and it is obvious that one shop will have many employees… then your schema for employees and shops is correct.

If an employee will be part of only one shop, then that many-to-many relationship is not necessary and you could drop the EmployeeShop table.

The SaleLineItemr and all the rest… I would need more information but it looks a bit over-complicated.

@molona, Thanks for replying to me. You made me day!!! Yeah it’s complicated when you get to have this many tables. My question basically was if my many to many relationships are logically correct and you answer my question, but I am still confused about the last table. I am not sure what to put in this table. Do you think you could help? Do you think it’s not needed? I also feel like you are right about if only one vendor is associated with specific types of orders and no other vendor can be related to that order, then it is a one to many relationship. I guess I will drop the OrderVendor table and then just connect the Order and Vendor as one to many relationship.

As I said, I would need more information because the whole thing from SaleLineItem onwards is confusing for me.

So I’m not surprised that you’re confused by the last table. I’m confused with the whole theme.

I suspect that you sell the ingredients that are needed for a particular recipe… but then, each product would be an ingredient and therefore the ingredient table would not be necessary anymore.

I would need a bit of clarification to know what you’re aiming for.

OK, I had to look at this several times because the naming conventions threw me off and I kept getting confused. But I think I have sussed out what you’re doing. A couple comments:

  1. Personally, I would add a FK link from Sales to Store as well. You know which employee made the sale, but you don’t know where the employee was when the sale was made. I could also argue that the employee shop lookup table would then be overkill, but it does allow for flexibility while allowing you to limit store/employee relations
  2. Where I get lost is this - What’s the difference between a product and an ingredient? It seems to me they are the same, and the “inventory” portion seems to bear that out. You order ingredients from vendors, but sell product? Or is product like a Hello Fresh type of product which is a whole meal? In that case, get rid of product and tie directly to the recipe.
  3. This looks like a start of a rudimentary inventory system. If that’s the case, then there are a number of design flaws that I can see…there’s no tie between store and product/ingredient (do you have what you need for employee Jane to sell from store Alpha?). How do you know how much of what quantity you have in stock (yes, you can sum orders, but you’ve then sold quantity to the customer)

A couple other notes which takes you down a different rabbit hole…

  1. The order side is messed up from what I can tell. An “order” should only tie to one vendor. If you want to track a “manifest” of multiple orders, fine but have a master list of orders in one bundle. You should be able to track an order down to specifically what you got from that vendor. Your design doesn’t allow that.
  2. IngredientName should be on the Ingredient table.
  3. There should be a quantity on the Order_Line_Item table.

Hey @DaveMaxwell,

Thanks for your input but I did get rid of the OrderVendor table so it’s down to 13 tables instead of 14 tables. But now I have a couple of questions and comments: how I am exactly supposed to connect the Sales table to the Shop table? The many to many relationship from the Sales table to Shop table will not look neat. How also will I am supposed to connect the store to product table, that will be unneat too?

The comments that I want to make are the following: First, you mentioned you didn’t understand why there were a Product table and Ingredient table. Let me explain. A product is for all the items that my dear friend Brenda wants to sell at her shop. For example, pantries, pies, cookies. The ingredients are what needed for the products to be made. For example, you would need 25 pounds of wheat flower, 4 cups of salt, 3 gallons of milk, and a dozen eggs to make a specific bread. Also, do answer you question, she does sell products and buys the ingredients from the vendors in order to make the ptroducts. Then after she makes the products, she sells them to the customers.

The sales to shop should be a one to many relationship. A shop can have many sales, but a sale should only come from one shop (see comment #3 above).

Regarding the product/ingredient…that’s kinda what I thought, so I’ll throw a follow up question then - what’s the difference between product and recipe? Aren’t they one and the same?

Based on your feedback…

  • I personally would get rid of the product table as it stands and rename recipe to product. But if you want to keep it, you can, but I think it’s a waste to have a behind the scenes table that is a 1:1 correlation to another table.
  • I’d move UnitsofMeasure and Price to the new product table. The size and price should always be consistent for the product, and the total price sold can be derived by multiplying the quantity on the line item but the product price.

@DaveMaxwell,

So you strongly think I should rename the product table to a recipe table. Oh, forget to answer your question. A product is all the items she wants to sell and a recipe is all the ingredients or a step by step instructions of how to make a specific item and tells quantity of how much to put in the specific item you want to make. A recipe has quantity and unit of measure (cups, teaspoons and how much of that unit to pour or mix with) . The SalesLineItems table already has the UnitsOfMeasure so the Products table can also have that too? Here is an update of my modified ERD model version of the correction you suggested for me to make.

Closer.

In essence, a product and a recipe are the same thing, just a different set of information.

Let’s use donuts as an example. For a store customer, they would want to know a description of the donuts, how many come in a standard order and the cost. For Brenda, she might want to know how it’s cooked (fried, baked), how long to cook it, and any special instructions. But they still want to know information about donuts. So this table would serve both “customers” (I know I’m using the same term twice here, but now we’re talking in terms of distinct users of the database)

Note: I’m using your naming convention while I personally would use the more traditional camel case (i.e. ProductID and ProductName). Personal preference, so if I slip and put it the other way, you’ll know why…
Note2: PK = Primary Key, FK = Foreign Key

Product

  • Product_ID (id, PK)
  • Product_Name (string)
  • Unit_Of_Measure (string)
  • Price (decimal/float depending on the DBMS)
  • Method_of_Production (string)
  • Special_Instructions (text or whatever your large text block datatype is)

As for Units_Of_Measure, I would leave it on product because that’s where it makes the most sense. You’re not going to order a dozen donuts one time and ten pounds of them another. You’re either going to order singles or in dozens, so you can store the unit as each and a dozen would just have a quantity of 12, where if the unit is a dozen, then you’re dealing with decimals (1 would be .083, 4 would be .33, etc)

Some things I notice in this iteration:

  • ProductIngredients needs a FK to Product
  • Orders has VendorID and Vendor_ID. You don’t need two FKs to the same table.
  • On OrderLineItems, you’re missing a FK to Orders
  • On OrderLineItems, the FK would be easier if it was just Ingredient_ID instead of Ingredient_Ingredient_ID. That would be a nightmare to maintain. A good habit is to be consistent, and I’ve found that naming the primary key as ID or PK and then on the other tables using a TableNamePK or TableNameID format makes life easier in the long run,
  • Are all these sales POS (Point Of Sale) or will she be taking pre-orders? If you’re taking pre-orders, there need to be dates added to the Sales table for that as well as a sale status (Paid, not Paid, Complete, Cancelled, etc)

@DaveMaxwell Thanks so much; you have been very helpful. My only question for you now is how do I possibly make a foreign key for Orders in OrderLineItem table? I would have to have them next to each other, right? In order for this to be able to happen. Just so you know Brenda’s Bakery hasn’t actually gone into business yet; she is about and wants me to try to help her out. Here is the updated ERD.

Same way you did from SalesLineItem to Sales. You put OrderID into Order_Line_Item and make it a one to many (one order can have many order line items).

  • I see that you haven’t included StoreID into the sales table. She doesn’t want to keep track of which stores are the most successful? Or what items sell best at which store? You’ll need StoreID in the Sales table to be able to pull that off.
  • You don’t need UnitOfMeasure or Price in the SalesLineItem table, Those are now in the Product table.
  • Again, if she’s planning on using this as an inventory system, you’re going to need to be a little more granular to ensure each store has enough inventory to allow for the sales to occur at them.

@DaveMaxwell,

You mean to make a foreign key for ShopID in the Sales table? That would make more sense. I have done that in the updated version of my model. Oh, FYI she hasn’t actually started the business; this is why we are still trying to figure out all the features that the database can provide her. Do you think my design is starting to look unneat?

Define “Unneet”

If you’re talking about the diagram, drag the orders and vendor table down to the bottom left and drag the shops up to the top right - that should clean it up. It’s a visual tool, and doesn’t have to look pretty. As long as it gets the job done.

If you’re talking about the database structure itself, I’m not sure there are many places you can simplify it. If it meets the business needs, that’s the important thing.

A couple comments I see.

  • date is a reserved word in SQL. You’d be better off naming it SalesDate or something.
  • Save yourself headache later and pick a naming convention. Some you have in Camel Case, and others have underscores. Guaranteed that’s going to cause you issues later trying to develop against it.
  • You don’t need vendor_id in OrderLineItem
  • You do need a UnitOfMeasure in ingredient, or at the very least ProductIngredient.

@DaveMaxwell,
Thanks for all your input. Here is another model update. Can you take a look at one last time? Thanks for being patient with my. A moved a lot of things around and took some suggestions. Any suggestions or is okay to start to forward engineer my model into a physical database?

You lost your tie between product and ingredients.

@DaveMaxwell,
If I want my model to in landscape instead of portrait form, how could I do that. I don’t have a PDF converter though. Any online tools that can do that? I haven’t been able to find any.

What tool are you using now?

@DaveMaxwell,
MySQL Workbench 6.3. I know that File > setup option and click on landscape it should be able to change the orientation of my model, but it does not work.

Sorry - I don’t use mySQL that often, and I almost never use the visual tools anyway, so I’m not going to be much help in this regard.

@DaveMaxwell,

I think I might have given you wrong information about the ingredient and recipe aspects of my model. I know a product can have a recipe, but what if I made an extra table called recipe? A product can have a recipe and a recipe can be for many products, but my question for you is would it make sense to have a recipe table since I said earlier that a recipe for any product is just all the ingredients that go into that product? So why would I need a recipe table if I can still track the ingredients from the ingredients table by having it connected to products table? As it stands currently, I have created the recipe table and made a relationship from it to the products table. Here is the model attached below.