Hey, guys, I'm working on building a database for my step-mom's chocolate shop. I just took a database course, and that was my inspiration, but clearly, I didn't pick up on how to address this issue. I am trying to create an appropriate data model, but I'm having some trouble modeling the purchasing/B2B aspect of the business. Here is what I've got so far:

I know that each ingredient she uses can be bought from multiple vendors, and each vendor sells multiple ingredients. This many-to-many relationship is then given a look-up table "Vendor_Ingredient" with the Vendor_ID and the Ingredient_ID as a composite primary key (or at least it's unique if you use a surrogate key) with price as an attribute.

I know that when she places a purchase order, it is only sent to one vendor. Each purchase order also has many ingredients on it, and any ingredient can also be on many purchase orders. I know that really what she is purchasing (in the sense of data modeling) is not just an ingredient, but an ingredient from a specific vendor at a price that's dependent on the ingredient and vendor. So, there is a many-to-many relationship between purchase order and "Vendor_Ingredient."

And this is where I'm stuck. If every purchase order points to only one vendor, but the purchase order also points to many vendor ingredients (through a lookup table, of course), how can you enforce the integrity that a purchase order's "vendor_ingredients" only point to the vendor to whom the purchase order is being placed? Because its an integrity issue, I figure I must be violating a normal form, but I know that all these tables are in 3NF and BCNF; I don't really know how to enforce integrity for the higher normal forms (if that's even the issue). If it's not a normal form violation, what can I do to make sure that a purchase order's ingredients are pointing to the same vendor as that to which the purchase order is being sent?

Thanks for all and any help!

in practical terms, by not choosing any other vendor's ingredients

a database application has to have a "front end" -- where the user makes choices

to create a purchase order, the user has to decide to do so, and initiate some action in the front end app to get the process started

when the user decides to place a purchase order with vendor X, then the front end app should only show vendor X's products from which to select, and these selections decide which rows get created in the vendor_ingredients table

simple, really

on the database side, you could use a CHECK constraint, but this would be needed only if you felt there was a possibility that you would get new vendor_ingredients from someplace other than your front end app and needed to ensure the integrity at the database level

Thanks for your prompt reply!

Perhaps it's just the theory of it that's got me hung-up, but for some reason I feel like integrity should be enforced completely at the database level. Is that an unreasonable expectation of the DBMS? You're right that there is (that I know of) no instance in which the front-end app wouldn't supply the vendor's ingredients, but I feel uncomfortable leaving the integrity of the model up to an application.

Also, how could a CHECK constraint work in this instance? You can't use a CHECK constraint across tables, so in my "PO_Vendor_Item" look-up table that links Purchase Orders to the Vendor's Items, how could you create a CHECK constraint without referring back to the "Purchase_Order" table to see the Vendor Foreign Key?

Let's say I want to enforce as much integrity as possible through the database. Could I use triggers? If so, how?

Again, thanks to all who read and reply!

If your mom can order a thinga-mabob from more than one vendor, I say let her buy it from whoever she chooses. Particularly for a small business, having the ability to purchase when/where/how they need to purchase is very important.

While it is technically possible to limit the choice, for this kind of business that would be a potentially fatal business mistake.

-PatP

No, I completely agree, I just wanted to make sure that each purchase order and each item on that purchase order was pointing to the one vendor. If she wanted to buy the same item from a different vendor she would just have another purchase order. But I figured it out. Here was what I had:

Vendor
--------------
Vendor_ID PK
...


Ingredient
----------------
Ingredient_ID PK
...


Vendor_Ingredient
--------------------------
Vendor_ID PK, FK
Ingredient_ID PK, FK
Cost

Purchase_Order
------------------------
PO_Number PK
Vendor_ID FK /* The vendor is here because each purchase order only belongs to one vendor. */
...

PO_Vendor_Item
-------------------------
PO_Number PK, FK
Vendor_ID PK, FK
Ingredient_ID PK, FK
/* The SQL for this being:
FOREIGN KEY (Vendor_ID, Ingredient_ID) references Vendor_Ingredient (Vendor_ID, Ingredient_ID),
PRIMARY KEY (PO_Number, Vendor_ID, Ingredient_ID) */

But, clearly, a PO_Vendor_Item tuple can have a PO_Number that points to a particular vendor, but it might not match the Vendor_ID in "PO_Vendor_Item."

Here was the solution I created, and I think it works much better. Any thoughts?

Vendor
--------------
Vendor_ID PK
...


Ingredient
----------------
Ingredient_ID PK
...


Vendor_Ingredient
--------------------------
Vendor_ID PK, FK
Ingredient_ID PK, FK
Cost

Purchase_Order
------------------------
PO_Number PK
Vendor_ID PK, FK /* See, here Vendor_ID is now part of the PK. This means we can use it as part of the foreign key in PO_Vendor_Item*/
...

PO_Vendor_Item
-------------------------
PO_Number PK, FK
Vendor_ID PK, FK
Ingredient_ID PK, FK references Ingredient_ID
/* The SQL for this being:
FOREIGN KEY (PO_Number, Vendor_ID) references Purchase_Order (PO_Number, Vendor_ID),
PRIMARY KEY (PO_Number, Vendor_ID, Ingredient_ID) */

P.S. the changes are marked in red in case you didn't pick that up! Thanks again for everyone who helped!

you could always use triggers, yes

i understand what you're saying about the CHECK constraint not working across tables, but i would try CHECK(SELECT MIN(vendor_id) = MAX(vendor_id)) or something like that

alternatively, you could use an ASSERTION, except of course no database system supports it today

Oh, no, wait. Crap. This still doesn't work. Now an item can be ordered from a vendor even if they don't sell that item. Back to square one. Any ideas?

How bout this solution:

Vendor
--------------
Vendor_ID PK
...


Ingredient
----------------
Ingredient_ID PK
...


Vendor_Ingredient
--------------------------
Vendor_ID PK, FK
Ingredient_ID PK, FK
Cost

Purchase_Order
------------------------
PO_Number PK
Vendor_ID FK /* The vendor is here because each purchase order only belongs to one vendor. */
...

PO_Vendor_Item
-------------------------
PO_Number PK, FK
Vendor_ID PK, FK
Ingredient_ID PK, FK
/* The SQL for this being:
FOREIGN KEY (Vendor_ID, Ingredient_ID) references Vendor_Ingredient (Vendor_ID, Ingredient_ID),
FOREIGN KEY (PO_Number, Vendor_ID) references Purchase_Order (PO_Number, Vendor_ID),
PRIMARY KEY (PO_Number, Vendor_ID, Ingredient_ID) */

Crap, I just copied and pasted the old one. Just pretend that Purchase_Order has a composite PK of PO_Number and Vendor_ID

which would allow you to enter PO_Number 1234 with Vendor_ID 567, and a separate row with PO_Number 1234 with Vendor_ID 789

Not if you include a unique constraint on the PO Number!

Yosarian,
What database platform are you using?
You will have a table of purchase order items linking purchase orders to vendor items. In SQL Server you could create an indexed view to enforce the constraint that a single purchase order can have items from only one vendor.

Hey Blindman,

To be honest, I'm using both Oracle and MySQL. On my laptop I'm using MySQL, but since (and as I conveniently left out) this project is for my final project for a class that uses Oracle, I'm really trying to make this work for Oracle.

I found a perfectly viable solution, though, I believe, using both Oracle and MySQL (or any DBMS, for that matter), so I'm gonna stick with that.

Here's a section of my code (for MySQL); tell me what you think:

CREATE TABLE Ingredients(
Ingredient_ID int(4) AUTO_INCREMENT PRIMARY KEY,
Ingredient_Name varchar(30) NOT NULL,
Description varchar(100),
Std_Unit_Amount float(5,2) NOT NULL,
/* Std_Unit_Amount should be measured in oz. for solids and fl. oz. for liquids. */
Units_to_Reorder int(2) NOT NULL,
Units_on_Hand int(4) NOT NULL,
Reorder_Unit_Point int(3) NOT NULL);

CREATE TABLE Vendors(
Vendor_ID int(4) AUTO_INCREMENT PRIMARY KEY,
Vendor_Name varchar(30) NOT NULL,
Main_Contact varchar(40),
Phone_Number bigint(10) NOT NULL,
Fax_Number bigint(10),
Address1 varchar(30) NOT NULL,
Address2 varchar(30),
City varchar(20) NOT NULL,
State varchar(2) NOT NULL,
Zip int(5) NOT NULL,
Comments varchar(50));

CREATE TABLE Vendor_Ingredient(
Vendor_ID int(4) NOT NULL references Vendors,
Ingredient_ID int(4) NOT NULL references Ingredients,
Cost float(5,2) NOT NULL,
PRIMARY KEY (Vendor_ID, Ingredient_ID));

CREATE TABLE Purchase_Order(
Purchase_Order_Num int(5) AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE Box_Order(
Box_Purch_Ord_Num int(5) PRIMARY KEY references Purchase_Order,
Date_Placed date NOT NULL,
Sales_Order_Num varchar(15));

CREATE TABLE Ingredient_Order(
Ing_Purch_Ord_Num int(5) NOT NULL UNIQUE references Purchase_Order,
Date_Placed date NOT NULL,
Sales_Order_Num varchar(15),
Vendor_ID int(4) NOT NULL references Vendors,
PRIMARY KEY (Ing_Purch_Ord_Num, Vendor_ID));

CREATE TABLE Ingredient_Order_Item(
Ing_Purch_Ord_Num int(5) NOT NULL,
Vendor_ID int(4) NOT NULL,
Ingredient_ID int(4) NOT NULL,
Quantity int(3) NOT NULL,
FOREIGN KEY (Ing_Purch_Ord_Num, Vendor_ID) references Ingredient_Order (Ing_Purch_Ord_Num, Vendor_ID),
FOREIGN KEY (Vendor_ID, Ingredient_ID) references Vendor_Ingredient (Vendor_ID, Ingredient_ID),
PRIMARY KEY (Ing_Purch_Ord_Num, Vendor_ID, Ingredient_ID));

scoundrel!!!