@ridders,
nope. it's in here
https://www.lucidchart.com/documents/edit/df7aa38c-0031-454b-a328-ae423cfb3375
nope. it's in here
https://www.lucidchart.com/documents/edit/df7aa38c-0031-454b-a328-ae423cfb3375
Learning Normalisation
- Thread starter jmq
- Start date
Steve R.
Retired
- Local time
- Today, 03:23
- Joined
- Jul 5, 2006
- Messages
- 5,729
In taking a look at your image of your structure, you have some structural problems. The field you use as a "Primary Key" should not be a field that could be changed, such as your "order id".
The purpose of a primary key is to link tables. If a Primary Key (PK) is changed, such as an order number, it will disrupt things to be polite.
The purpose of a table is to contain all items (attributes) that describe a certain thing, such as a product. Consequently, your product table would contain a Foreign Key (FK) for suppliers and a FK for prices. This can be complicated if there are multiple suppliers, multiple prices, and if historic prices need to be tracked. It will be up to you to design the database to address those issues.
I suggest that you do an internet search on "normalization". You will get many hits.
I found this book to be very helpful: "Base Tutorial: From Newbie to Advocate in a one, two... three!". This is not an MS Access PDF, but it still serves the purpose of teaching/learning "normalization".
The purpose of a primary key is to link tables. If a Primary Key (PK) is changed, such as an order number, it will disrupt things to be polite.
The purpose of a table is to contain all items (attributes) that describe a certain thing, such as a product. Consequently, your product table would contain a Foreign Key (FK) for suppliers and a FK for prices. This can be complicated if there are multiple suppliers, multiple prices, and if historic prices need to be tracked. It will be up to you to design the database to address those issues.
I suggest that you do an internet search on "normalization". You will get many hits.
I found this book to be very helpful: "Base Tutorial: From Newbie to Advocate in a one, two... three!". This is not an MS Access PDF, but it still serves the purpose of teaching/learning "normalization".
Last edited:
@Steve R.
Thank you for having a time to reply/suggest/recommend
i have downloaded the book and will try to study it out.
really appreciated!
[orderID] - it's an Autonumber. it cannot be changed
[product] - can be supplied by many supplier with different prices and unit. so i created a junction table ProductPrices
Historical Price - definitely yes! i need to track the prices to know the trend. but I DONT KNOW HOW TO DESIGN IT.
Thank you for having a time to reply/suggest/recommend
i have downloaded the book and will try to study it out.
really appreciated!
[orderID] - it's an Autonumber. it cannot be changed
[product] - can be supplied by many supplier with different prices and unit. so i created a junction table ProductPrices
Historical Price - definitely yes! i need to track the prices to know the trend. but I DONT KNOW HOW TO DESIGN IT.
- Local time
- Today, 03:23
- Joined
- Jan 23, 2006
- Messages
- 15,565
Here is a free, generic model from Barry Williams' site that may help with tables and relationships for Customers, Orders and Partial Deliveries.
This is a generic model in that it deals with typical entities. You can add/remove alter the model to suit your needs. If you study the tables and relationships, you should be able to see how your set up compares.
For anyone interested here is a link to more info on the Lucidchart free account.
Good luck.
This is a generic model in that it deals with typical entities. You can add/remove alter the model to suit your needs. If you study the tables and relationships, you should be able to see how your set up compares.
For anyone interested here is a link to more info on the Lucidchart free account.
Good luck.
Last edited:
- Local time
- Today, 03:23
- Joined
- Jan 23, 2006
- Messages
- 15,565
My best guess is that an Order may consist of multiple items. But since it is a partial delivery/shipment, only those items in the delivery/shipment are identified, and are also invoiced.
So the Order has many items.
Some items from the Order are delivered in a shipment and are invoiced
Many shipments are required to deliver all items in the Order
Invoices from all shipments constitute the Order Invoice
In his model he also accommodates CreditNotes for Returned Items
You may not need all of these tables/relationships, but it is a generic model that someone built and published as an aid to the rest of us. It may not represent your business processes.
Good luck.
PS: What type of Lucidchart account do you have?
So the Order has many items.
Some items from the Order are delivered in a shipment and are invoiced
Many shipments are required to deliver all items in the Order
Invoices from all shipments constitute the Order Invoice
In his model he also accommodates CreditNotes for Returned Items
You may not need all of these tables/relationships, but it is a generic model that someone built and published as an aid to the rest of us. It may not represent your business processes.
Good luck.
PS: What type of Lucidchart account do you have?
Last edited:
- Local time
- Today, 03:23
- Joined
- Jan 23, 2006
- Messages
- 15,565
Your deliveryDetails does not identify Product (or OrderItem).
Also a question for your "business", you purchase Products from Suppliers. You have a purchasePrice for each SupplierSuppliedProduct, and that Price can change from one Purchase to another. You also sell those Products to Customers, so you have some sort of SellingPrice, which can also change from time to time.
I recommend you include the AgreedToSellingPrice with the OrderDetail record.
You can make up some sample data, and some business scenarios,then test your model to make sure you can store and retrieve the required data.
What type of Lucidchart account are you using?
I remember helping someone with CustomerProductsDelivery and Receipt of Goods(attached has some of same features as you're using)
Also a question for your "business", you purchase Products from Suppliers. You have a purchasePrice for each SupplierSuppliedProduct, and that Price can change from one Purchase to another. You also sell those Products to Customers, so you have some sort of SellingPrice, which can also change from time to time.
I recommend you include the AgreedToSellingPrice with the OrderDetail record.
You can make up some sample data, and some business scenarios,then test your model to make sure you can store and retrieve the required data.
What type of Lucidchart account are you using?
I remember helping someone with CustomerProductsDelivery and Receipt of Goods(attached has some of same features as you're using)
Attachments
Last edited:
- Local time
- Today, 03:23
- Joined
- Jan 23, 2006
- Messages
- 15,565
Suppose you have an Order Id 1000, and it has 5 OrderItems a,b,c,d,e.
If you have to Deliver OrderId 1000 items a,c,d ---I think you have to include those in the DeliveredItems table (If you have a Bill of Lading or some list identifying what was delivered, I think you would want it and even a copy signed by the Receiver. You wouldn't want the Receiver to come back and say you didn't deliver c,d - Now you have "proof" and Receiver's signature.
If you have to Deliver OrderId 1000 items a,c,d ---I think you have to include those in the DeliveredItems table (If you have a Bill of Lading or some list identifying what was delivered, I think you would want it and even a copy signed by the Receiver. You wouldn't want the Receiver to come back and say you didn't deliver c,d - Now you have "proof" and Receiver's signature.
jdraw,
how about this?
https://www.lucidchart.com/invitations/accept/cdbfd94e-7bc4-43bf-bbf2-5f69c8b848d5
how about this?
https://www.lucidchart.com/invitations/accept/cdbfd94e-7bc4-43bf-bbf2-5f69c8b848d5
isladogs
MVP / VIP
- Local time
- Today, 08:23
- Joined
- Jan 14, 2017
- Messages
- 18,968
I created a free account and checked the link out of interest.
It worked but was very slow to load for some reason. Over 30 sec
Its a very neat tool....
Does the fact that its a shared link mean it can be edited by others?
I haven't as I'm testing on my tablet.
I'll leave Jack to comment on the design as I don't want to tread on his toes.
It worked but was very slow to load for some reason. Over 30 sec
Its a very neat tool....
Does the fact that its a shared link mean it can be edited by others?
I haven't as I'm testing on my tablet.
I'll leave Jack to comment on the design as I don't want to tread on his toes.
Last edited:
- Local time
- Today, 03:23
- Joined
- Jan 23, 2006
- Messages
- 15,565
I looked at your link and was invited to share the diagram.
At this point you have an evolving logical model. You might want to list your business facts and verify that each is represented in the model. After that 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.
At this point you have an evolving logical model. You might want to list your business facts and verify that each is represented in the model. After that 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.
jdraw,
i hope lucidchart could allow a collaboration of designer in 1 drawing though so i can see exactly what you want to have.
i will try to test (stump the model) when this diagram is in good shape. it's really hard to redesign it again with all the relationships set.
i have here the 4th version of my drawing based on your suggestion.
please see if i have it right. attributes at the right entity.
thanks!
https://www.lucidchart.com/invitations/accept/cdbfd94e-7bc4-43bf-bbf2-5f69c8b848d5
i hope lucidchart could allow a collaboration of designer in 1 drawing though so i can see exactly what you want to have.
i will try to test (stump the model) when this diagram is in good shape. it's really hard to redesign it again with all the relationships set.
i have here the 4th version of my drawing based on your suggestion.
please see if i have it right. attributes at the right entity.
thanks!
https://www.lucidchart.com/invitations/accept/cdbfd94e-7bc4-43bf-bbf2-5f69c8b848d5
