I am new to databases, obviously a beginner, previously been working in MS Excel. I have done some self study on databases and now have started working on a database in Access 2010.
Basically, I am designing a new database which will work as follows.
1. Material will be booked by the purchasing staff with the supplier via a broker at a certain rate and delivery condition [BookTerm]. All these details shall go into the table [tblBooking].
2. Suppliers then start to send the material.
Material is loaded on trucks in bags of different weighments.
The whole truck is weighed at the weighstation [GrossWt].
The Material is then unloaded from the truck.
Each and every bag of material is checked and gets a pass/fail.
Material that passes is assigned a GroupNo to differentiate it from other material.
Rejected Material is either loaded back on truck (and is not counted for) or kept on floor for later return. Material bags rejected and kept on floor will be counted and entered in table [tblRejBag]. The weight of the rejected bags lying on floor will be deduted from material weight.
Empty truck or truck containing rejected material [VehicleWt] is then weighed at the weighstation to get material weight.
All this information goes in tables [tblArrivals], [tblArrivedBags], [tblArrBag] and [tblRejBags].
Hope this explains the lot.
I want to check whether my design is normalised as it should be according to normalisation rules???
As further, I shall be building forms, queries and reports later on.
Each bag relates to a booking (which relates to terms and brokers and suppliers). The part on the left looks ok. The arrival side, however, does not look right.
Without getting too much into the weeds, I noticed each bag is either accepted or rejected - that's 1 table with a boolean, not 2 separate tables. And, that information is really related to the arrived bag, so the fields should be in the ArrivedBags table.
If I understand:
1 Arrival to Several (many) Bags
Each bag can be accepted or rejected.
Accepted get assigned a Group Number and get added to an inventory.
The right side should only be 2 main tables: tblArrivals and tblArrivedBags
tblGroupDetails is part of the Arrivals data
Accepted/Rejected, weight, etc. relates to the Bags
The group number only applying to accepted bags sounds like an "inventory" marker. If so, then your intermediate tables (arrivals) are kind of like a receiving log, or transaction history, and therefore your final table should be an inventory table, with additions relating to the transactions that put them in stock (the group number is a lot number).