Results 1 to 7 of 7
  1. #1
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112

    DB structure when records are combined for some information and not other


    Hello,
    I have a database where every record is a property owned by my conservation group.
    The fields include such things as property location, size, stewardship plans, taxes, etc -- on each individual property.

    However, for some other data, the properties are grouped: e.g., if one donor gives us three properties at the same time, we would issue one receipt, have one invoice for legal services for the whole transaction, one combined appraisal, etc -- and the grouped properties then have one asset value in our financial records.

    I am unclear about the best way to organize this in the database. Obviously there are problems if each of the properties has the data since any report would be "double counting" expenses, etc.

    I was thinking perhaps designating one of the properties as a "spokes-property" for the combined information, and some reports could be on that subset. But I need a way to link each property to it's group and o inspect the relevant information for each of the properties.

    And there may be issues here that I haven't anticipated.

    Any suggestions and feedback would be appreciated! Thanks.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    think you need to revisit your understanding of normalisation. Your current design may be relevant to one receipt, one property but not one receipt many properties

    you need at least 2 tables

    tblReceipts - detail of receipts
    tblProperties - detail of properties including a FK to the receipt PK

    and if you have the situation where two or more receipts can relate to one or more properties, you need a 3rd linking table to represent the relationship

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use subtables.
    tClient table (master table)
    w ClientID

    tClientProperties table (sub tbl)
    has fields:
    tPropID (autonum)
    ClientID (long) from tClient table
    Addr
    city
    zip etc...

    tReceipts table would be like a shopping cart, add many items to it.

    tReceipts table ('master')
    RcptID (auto)
    ClientID (long)
    RcptDate
    PayHow


    tReceiptItems table
    RcptItm (auto)
    RcptID (long)
    Item
    Amt
    Qty

  4. #4
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112
    Hello,
    I am just now getting back to this problem...
    As suggested, I have set up sub-tables, with this arrangement:
    TBL-AcquisitionEvent (1) to TBL-Properties (many) (A land donation may include several properties)
    TBL-AcquisitionEVent (1) to TBL-Expenses (many) (A land donation may include multiple expenses)

    I have an Event Form, with subforms for Properties and Expenses.

    BUT the TBL-Properties also includes some properties that do not have an AcquisitionEvent -- (e.g., we steward the property but don't own it, it's a public property, etc.)
    This is causing trouble with the referential integrity of the first of these relationship because there are some properties that are not included in event table.

    Should I not do referential integrity? Should I divide the property table into those with and without event?
    Any help and suggestions would be appreciated.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Referential integrity is advised but not essential although you will need to still need to manage that with code.the fact it is causing problems is indicative that your dB design does not meet the real world requirement so a further redesign needs to be investigated. It may be as simple as reversing the relationship I.e. the property becomes the parent and the acquisition table the child

  6. #6
    louise is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    112
    Thanks for your thoughts on this.
    Can the "many" side be the parent?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no, parent is on the one side - suggest you investigate the many to many relationship

    A property can have many receipts and a receipt can have many properties.

    Can doesn't mean there has to be any, or might have only have one. It requires a third table

    tblProperties
    propertyPK
    propertyName

    tblReceipts
    ReceiptPK
    ReceiptIdentifier

    tblPropertyReceipts
    PropertyRecieptPK
    PropertyFK
    ReceiptFK

    tblPropertyReceipts is the child of both the other two tables

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 08-27-2020, 04:30 AM
  2. Replies: 1
    Last Post: 07-31-2018, 05:06 AM
  3. Replies: 1
    Last Post: 02-15-2016, 02:48 AM
  4. Replies: 4
    Last Post: 11-04-2013, 02:24 PM
  5. Question on database structure - limiting linked records
    By andrewb in forum Database Design
    Replies: 2
    Last Post: 03-22-2012, 05:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums