Learning Normalisation (1 Viewer)

or much better to link it directly to deliveryDetails.

Perhaps you don't want the receipt?? This comes down to your "business process" for deliveries.
Does your business have partial deliveries?

Think of what might be included on such a Receipt of Goods Delivered/ Could be a copy of an Invoice for Partial Order

DeliveryDate
CustomerName
CustomerAddress
OrderID
OrderDate

For each Item from the Order that was Delivered
ItemName
ItemQty
ItemUnitSellingPrice

ItemReceived (checkbox?)

Typically, the "deliverer" gets the recipient to sign/initial a document acknowledging that these goods were received.

of course i want a receipt. i just can't figure it out in my model yet.

jdraw,

i think i get it. table deliveries will be the receipt?

like this?

Re my post #42. The Receipt isn't necessarily in any one table.
If you can identify what a receipt should contain in plain English, then "with your stump the model hat on" can you get the information for the receipt from the values in your model.

For example:

Consider the Item needed and the table(s) involved to get it

Going through this process will help refine your model.

jdraw,

Yes. That wasn't intended as a question. As I was identifying potential data for a Receipt of Goods form, I was trying to highlight that a signature of the Receiver (Customer, agent....) was recorded.

The key point here is:
When reviewing your business processes, identify all of the inputs and outputs ( forms, reports..), to determine what should be recorded (in the database, on a form..). With stump the model you use your test data and model and work through test scenarios following the business processes. You look for a few things such as - do the business processes "flow"; based on each process does the current data model allow storage and/or retrieval of the data needed in the process; are the attributes/fields in tables correct;

Any time there is an issue ---sort out the root cause-- is it the data; is it the model structure; is it the business process; is it the test scenario???? Determine the cause. Adjust the model/data/process/scenario to remove the issue; then test again. It's much easier to test this on paper than to build a physical database, then adjust that physical database as "issues" arise. Many problems in physical databases can be traced back to design issues (normalization, relationships, poor naming/definition, disjoint/incomplete business processes, etc)

Good luck with your testing.

jdraw said:

I suggest you create some test data and scenarios and play stump the model.

Here are some of the facts --you can add/adjust/remove as needed. Note I have used Products and Items in the facts. I have not included Invoicing since it isn't in your model. I did not include Dates with Order or Deliveries, you can add details where needed.

You have a number of Suppliers
A Supplier may supply 1 or more Products
Each Supplied Product has a PurchasePrice
You have 1 or more Customers that can be uniquely identified.
A Customer creates 1 or more Orders
An Order may contain 1 or more Products
An Ordered Product has a SellingPrice
You may negotiate an AgreedToPrice for a Product with a Customer (sale/clearance/loyalty)
An Order may be "pickedUp"/"carried out" by the Customer
An Order may be Delivered to Customer (may consider a ShipTo address)
Delivery(s) may be for full or partial Order (all or some of the OrdererProducts)
A Delivery is made by a Courier
A Delivery includes a List of DeliveredItems
The Customer is provided the list of DeliveredItems
The Customer verifies the receipt and signs a copy of the List of DeliveredItems

For scenarios you could consider the following, and adjust as necessary to create "business scenarios". You could add more complexity, but suggest you start with this and add detail as the model gets vetted.

Supplier X offers Products P1, P2 and P3 with unit prices $20, $25 and $40 respectively.
Customer CustJim makes an Order for 5 units of P2 and 1 unit of P3 and wants it delivered.
For some reason only 3 units of P2 are available. So FedY -the courier company- makes a partialDelivery of 3 units of P2 and 1 unit of P3 to CustJim.

You can test your model using pencil and paper.

NOTE: I use AgreedToPrice as the Price a unit of Product when sold to this Customer on this Order.
This allows you to change Product prices. It preserves historical Order info related to Prices paid for Product(s). It also allows you to have special prices for certain Customers; loyalty programs; clearance sales and Specials etc...

Good luck.

Click to expand...

It's crazy doing stump alone!

I don't have employees.
I don't have stuff
I don't have anyone who cares to do a stump but me.

lol

What is your goal? Are you learning Normalization? Or building some sample models? Or building a database application?

Did you try some sample data and scenarios?
You could even build (on paper) some forms (OrderForm) and fill them in and run it through your scenarios and model. Can you get from Customer Order through to Partial or single delivery without issue?
If there is an issue(s) can you tell us more?

It's difficult when you're working alone --mainly because you "assume things".

FYI, the entity relationship videos by LucidChart are very good. These are short, not a lot of theory and deal with cardinality which many videos do not.

There are a number of youtube videos on Normalization (various quality of presentation and content).

jdraw,

my goal is to create a professional database one day.
starting from paper model, to er diagrams, to flowchart of system. so far, everything works for this model. i will try to build the physical db now.

with the videos, i can probably watch that on weekends if i can. i'm doing this in the office and i have very limited access due to all the restrictions laid by the ITs.

I'm really grateful to stumble upon this forum and have all your help!