Table Issue - Relationships (1 Viewer)

NickFazer

New member
Local time
Today, 13:13
Joined
Apr 9, 2012
Messages
20
Nick --Fruit Management --CallOffContracts.

Draft rules: (these are preliminary and need to be refined and vetted)
Our company deals with many Suppliers
Many Contract(s) are set up with a Supplier
A Contract defines the Products and Quantities to be Supplied (other Terms/conditions?). No other relevant Terms and Conditions
A CallOff is a PurchaseOrder against an active Contract. The Purchase Order is the contract - for the purposes of this database the Supplier has agreed the contract and the PO is in place for product to be called off against it.
Many
PurchaseOrder(s)/CallOff(s) may be placed against a Contract with Supplier
A PurchaseOrder may be for a proposed Quantity of 1 or Many Product(s)
A PurchaseOrder identifies the ShipTo Location. YES
A Supplier ships many Product(s) as a Shipment
A Shipment has many ShipmentDetails
A Shipment identifies the actual shipped Quantity of Product. YES
A Shipment identifies the Container holding the Product. YES
A Container hold MANY Lots/Batches of shipped Product
A Shipment identifies the BestBeforeDate of each Lot of shipped Quantity of Product. YES
The PO proposed quantity of Product may differ from the actual shipped quantity of Product. YES - some unit quantities vary e.g drums of pulp will have different weights say +/- 5kg
The actual shipped quantity of Product is subtracted from the Contracted quantity. YES
A Lot(Batch) is a quantity of shipped Product with a common ProductType and BestBeforeDate. YES
A Shipment is separated into 1 or more Lot(s) based on ProductType and BestBeforeDate (by whom?) ME - The loading documents from the supplier have the information, I enter the data and a unique [incremental] Lot No is created.
A Shipment may contain 1 or many Pallets
A Pallet may contain 1 or Many Lots. Usually 1 and should be 1 but does happen occasionally
 

NickFazer

New member
Local time
Today, 13:13
Joined
Apr 9, 2012
Messages
20
From this statement:
Once I have all the details required I send this information to the Storage supplier and request a Booking in Date / Time and Reference for the Suppliers haulier to arrive with the goods.

It is not clear :
-who divides a shipment into Lots. - I do, it is a requirement of our QA system, the supplier documents that on the container loading paperwork and I will input it when I receive the documents after shipping.
-who the Storage supplier is and relationship to Supplier. No relationship to the supplier, the storage supplier is a third party warehouse close to our factory, they employed by us to store and issue stock on request [next stage of my project].
-can a shipment involve multiple Storage suppliers? No
-details of Storage handling (if relevant?) Not relevant stock arrives on pallets and is located in storage on pallets, any handling is when we ask for stock to be issued to the factory
-your interaction with Supplier re haulier. I manage the delivery of the container with the haulier / fowarding agent and the storage facility, I arrange a date / time and booking reference for the haulier to deliver and I provide the storage facility with all the information necessary to receive the stock and raise Goods In paperwork, that is the sent to us for booking in on our stock system
-details of Booking/Appointment (who does what, where, when?) Dealt with above, I enter the information as it becomes available and distribute it to the necessary people before the shipment arrives at storage.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Jan 23, 2006
Messages
15,361
Here is a draft model that you can use with some test scenarios. It should help with tables and relationships. I have not added proposed field names to the entities. The IX_.. fields are supplied by the modelling software to account for the relationships between these draft entities.
Good luck.
 

Attachments

  • FruitMgtStrawman.PNG
    FruitMgtStrawman.PNG
    45.6 KB · Views: 95

vhung

Member
Local time
Today, 06:13
Joined
Jul 8, 2020
Messages
235
Hello

I have been creating a database to help manage call off products from suppliers and until now I have built a structure that works, as far as the flow of data is concerned. I am able to enter all the data directly in the tables cascading down from entering a PO with a Supplier to Calling Off product and then entering details of the product shipped. However when I tried to create a shipping order to the Supplier I was missing a Shipment table and I am struggling to decide how to link this in currently it is linked to the Supplier table but there isn't a direct relationship to the call off items. I have attached the relationship diagram. The point of the exercise is to be able to call of fruit and manage the shipping details and provide the storage suppliers with the information required before delivery and for me to be able to look up what orders are placed / shipped / delivered etc. Would appreciate some advise on the structure of the data, I am starting to think that I have approached this from the wrong angle.

I will of course answer any questions regarding the flow but the tables should have the correct data but maybe I have should link it together differently.
Hello

I have been creating a database to help manage call off products from suppliers and until now I have built a structure that works, as far as the flow of data is concerned. I am able to enter all the data directly in the tables cascading down from entering a PO with a Supplier to Calling Off product and then entering details of the product shipped. However when I tried to create a shipping order to the Supplier I was missing a Shipment table and I am struggling to decide how to link this in currently it is linked to the Supplier table but there isn't a direct relationship to the call off items. I have attached the relationship diagram. The point of the exercise is to be able to call of fruit and manage the shipping details and provide the storage suppliers with the information required before delivery and for me to be able to look up what orders are placed / shipped / delivered etc. Would appreciate some advise on the structure of the data, I am starting to think that I have approached this from the wrong angle.

I will of course answer any questions regarding the flow but the tables should have the correct data but maybe I have should link it together differently.
feels so tired with table relationships
>to begin with table realationship is a draft of data flow
but then if it is necessary to connect every table or some tables
should create a look-up field to your connection,
the other way is to create a common field name for perfect key connection
>in some ways "Form" table source would matter you could not view data to a subform
else that common field should be created first
so that your desired form_data would be plainly viewed as you connect each master key
 

Users who are viewing this thread

Top Bottom