Tracking export of materials - Max amount per destination?

[mike_980]

I am adding a section onto an existing database so that our farm can log the maximum amount of slurry manure spread on each field (I will call destination to avoid confusion from now on). Each destination has its own maximum amount of slurry that it is legal to spread per 12 month cycle and the tanker we use to export the slurry is 11 cubic meters. At present I have considered the following layout for my tables:



tbl_destination:
Destination_ID
Destination_name
Land_area_for_spreading
Max_slurry_PA <---- this value will be in cubic meters


Tbl_spreading:
Spreading_ID
Destination_ID
Date_spread
No_of_loads <-----based on 11 cubic meters per load


We also have a slurry lagoon which we take slurry to when the fields are full of crops which holds 3500 cubic meters, i thought about adding this into the destination table and adding a tick box on the input form if the slurry was going to the lagoon but the problem is the lagoon is emptied again about every 6 months so the slurry stored there is then taken to the fields so it will need its own table with a single record I assume?

Something like this?

Tbl_lagoon:
Lagoon_ID
Lagoon_volume




This database is an extension of my current system which tracks fertiliser and to achieve that all inputs and outputs are stored in seperate tables then queries calculate the difference between the inputs and outputs to get the current stock levels in the store. The problem here is that the spreading limits are cleared every year so the current slurry volume on the fields on a given date will effectively return to zero.

If anyone could offer any kind of advice as to the best way to attack this so I don't make my usual mistake of setting about the thing wrong from the start I'd be very thankful!

[ItsMe]

It sounds like Tbl_spreading will handle the event of spreading the fertilizer. There should not be any issue of loosing info on an annual basis if every spread event is logged. It would just be a matter of querying the table correctly. In fact, Max_slurry_PA may end up being a calculated field. There must be a formula for this that only needs the value X where X = Land_area_for_spreading.

none the less, storing max_slurry is not the worst thing. As for the additional collumns or less in tbl_destination, I would only want to consider the ability of a field changing its size. Maybe you wont' spread fertilizer to the whole area and this would change the formula/result.

Not sure what Tbl_lagoon is doing. Maybe if you had two or more lagoons you could ID them and place a max volume in their respective records. You could then have another table log events of addition and subtraction. If it is just one lagoon, maybe additional columns to manage addition and subtraction events. Maybe a wheather index to help an estimation formula.

[mike_980]

Thankyou for the reply that's really helpful!

Since yesterday I have got more information on what needs doing and it looks like we should start the new season on the 1st of January every year which makes things easy. However, the maximum amount allowed to be spread on each destination is governed by the size of the destination in hectors, the type of soil eg sandy, clay, shallow and deep and the nitrogen content of the slurry being applied which we have tested every 6 months. The nitrogen contect doesn't significantly change we just get it tested because of legislation so it is fair to say that what is in the lagoon is the same nitrogen content as what is in the stores on the farm.

I think I will create a single record with current nitrogen content logged as a single record, then the user changes this when new results come in with a simple form I will make, then the queries can use this record to calculate the amount of slurry which can be applied.

So we have the destinations which will each have an N_max value in table to represent max nitrogen per hector and the total hectors available which then once we know the slurry N content we can calculate how many kg of slurry each field is allowed.

I am typing this more as a thought process to get it straight in my head does it sound OK to everyone? I have made too many databases which have been wrong from the moment I made my first table to know the importance of planning it properly before you start!

[ItsMe]

I would track nitrogen tests in a separate table. You can relate results from Nitrogen (one table) and relate hector values from a different table to calculate the formula. Placing Nitrogen calculations inside the table with Hector values will most likely cause conflict. nitrogen fluctuation and Hector fluctuation are on two different scales/schedules. Hence, two different tables.

Also, it sounds like you can have other tables to account for classifications of soil types. A table named tblSoilTypes would not get updated often if ever. You can have an ID field for the Primary Key and a field for description. Maybe half a dozen records, Sandy Loam, Deep, etc. It may get more comlex if the types can be "Many". Then you start getting into a Many vs Many relationship and your formulation becomes a bit of a headache.

Build it out simple first. Pretend some of the crazy business rules don't exist. Then you can see how applying the business rules may or may not break the DB as it sits.

[mike_980]

I would track nitrogen tests in a separate table. You can relate results from Nitrogen (one table) and relate hector values from a different table to calculate the formula.

Sorry I didn't explain very well, yes that was my plan

So really what I need is:


tbl_Destinations:
Primary key and list of all fields, list of all field sizes - The field size will never change, the database is only to calculate how many loads we can take to each field and spread evenly so I am tempted to keep this format unless you can see any other issues?

tbl_SoilTypes
different types of land (max 5 of 6 possible soil types)

tbl_test_results:
Containing Nitrogen, phosphorus and potassium results

tbl_spreading:
Same as first post


I am still a little unsure as to how to handle the lagoon as at present there is only 1 but it is possible there will be another added in the future. I think having a table to add slurry to the lagoon and a separate for removing it from the lagoon, then use a query to calculate the difference between them. I used that technique to manage bags of solid fertiliser stored in sheds on the farm and it seems to work well with just a few bugs in the queries which do not return a result unless there is a value in both tables. At the moment I am having to add a record to show zero bags have been removed to get the query to show any results.


Once again thanks for your help it is really useful!

[ItsMe]

I would just have one table for lagoon for now. Just add more columns. After you get another lagoon, you will want another table.