I am writing a farm management program for my father, from year to year fields change. What is the best way to keep track of fields and allow them to be split and merged from time to time yet keep a history of previous fields. basicaly like this. year one you have field a and field b, but year two you merge field a and b into field c. year three field c is split and becomes a and b again.

any ideas, can you point me in the right direction to learn how to handle these types of database design ideas.

Travis

Field is probably a combination of location plus dimension. Also, fields dont change too often. Probably only seasonal.

In this case there is no direct correlation between number of fields in a location, so you cannot maintain history of fields. But assuming locations are of fixed size, then you could have a current list for each location. Each time the fields in the location are reorganised, you should make a current copy of the full list for the field and a reference date to access the history when needed.

End

It's quite a difficult problem you've set yourself.

Location co-ordinates might work but I can't picture a farmer using them in day to day life.

Could he name just the smallest sections (of each field) ie field a and field b and accept that he couldn't break them down further? If so then something like these tables might work:

• Sections : name
• FieldSections : field_name, section_name, from, to
• Field : name, from, to, crop, ...
• FieldLog : field_name, log_time, log_txt

Example data:

Sections
=====
field a
field b

FieldSections
========
field a, field a, year 1
field b, field b, year 1
field c, field a, year 2
field c, field b, year 2

Field
===
field a, year 1, potatoes
field b, year 1, carrots
field c, year 2, peas

FieldLog
=====
field a, year 1 feb, ploughed field
field a, year 1 mar, planted potatoes
field a, year 1 jun, pesticide
field c, year 2 mar, planted peas

I only put the year in the examples above but my be better to have start and end dates. Not sure how useful the Field table is apart from as a lookup. Could use a neighbouring sections table that ensures you only can join fields that are physically next to each other.

Not sure if these tables work for you but at least it's a start

Mike

SQLSVR 2008 has built-in support for geospatial data, but I have not dived into that yet. It would definitely be pretty advanced stuff for someone new to database development.
I think your best bet may be to divide the property into the smallest atomic units that you can. Ones that will presumably never be split. And then combine them from year-to-year into larger groups.

I had no idea of using location as a geo-spatial co-ordinates. I only meant locations to mean distinctly characterisable and contiguous land areas which the OP agrist may feel familiar with like : locationa_aquired_in_19nn, locationb_rocky, etc. I think the OP use "fields", I meant aggregation of fields - if possible.

As far as the title of the thread is concerned, "Merge / split" records has meaning only if you have already decided the file structure but I think the OP wants I gave my idea.

End