Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009

    Question Merge / Split records

    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.


  2. #2
    Join Date
    May 2009
    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.


  3. #3
    Join Date
    Jun 2007
    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:
    field a
    field b

    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 a, year 1, potatoes
    field b, year 1, carrots
    field c, year 2, peas

    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


  4. #4
    Join Date
    Jun 2003
    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.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  5. #5
    Join Date
    May 2009
    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
    any ideas, can you point me in the right direction to learn how to handle these types of database design ideas.
    I gave my idea.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts