Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: Bad practice - populate a Table based on a calculated field in a Form

    I have read that it is bad practice to populate a Table based on a calculated field in a Form? Not sure why?

    But, I want to create my database with good practice and that is why I am asking for guidance, please?

    I have attached a sample database to explain it easier (it would be great if any of you could take a look)? Please go to "Contract Form" and/or "Contract Table" to see what I mean.

    Situation:-
    1. The "Contract Table" has two fields called "Property ID" and "Owner ID".
    2. The "Contract Form" has the same two fields.
    a) "Property ID" is chosen from a ComboBox (Property Table Query)
    b) "Owner ID" is automatically populated by a calculated field (PropertyTable Query)

    How can I get the "Owner ID" populated in the "Contract Table" by using good practice??

    Thanks you to all who attempt to help me solve this problem?
    Attached Files Attached Files
    Last edited by reddevil1; 09-12-11 at 08:24.
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so there are a number of problems here.

    First, you should get into the habit of enforcing your relationships.

    Second, you need to clearly define the nature of your relationships... how many owners does a property have? How many properties does an owner have? Do the owners only need to be linked to the property or to the contract? All these questions and more need to be answer and then 1:1, 1:M or M:M relationships need to then be used where appropriate.

    You shouldn't be worried at this stage about how to populate a table until the design is correct.

    To answer your question generally, I would say that there is no reason to populate the OwnerID in the contract because the contract is linked with the property, which, in turn identifies the owner(s).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    examples of bad practise is where you store derived data and or duplicated data..

    but if you are recording a piece of data which is relevant and say forms part of a foreign key then its fine

    so from what you've outlined so far I don't think you are doing anythign 'wrong'
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    StarTrekker,

    Thanks very much for reviewing my sample databse and for your clear advice.

    First, you should get into the habit of enforcing your relationships.
    I would like to get the design correct before I go any further, so i will read up on enforcing relationships - I hope this will help in this instance?

    Second, you need to clearly define the nature of your relationships... how many owners does a property have? How many properties does an owner have? Do the owners only need to be linked to the property or to the contract? All these questions and more need to be answer and then 1:1, 1:M or M:M relationships need to then be used where appropriate.
    I am terrible with my knowledge of relationships however, i will try and play about with the 'drag and drop' wizard in Access and see if this helps. Wish me luck

    I would say that there is no reason to populate the OwnerID in the contract because the contract is linked with the property, which, in turn identifies the owner(s).
    My thinking was to make the Contract Table a 'master table' where I could easily find my four main fields all in one Table (eg. Property ID, Owner ID, Renter ID and Contract ID). Then i coudl easily find the information for my many, many reports that i am ready to create?

    Thanks again.

    Paul
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    Healdem,

    Thank you very much for your input.

    examples of bad practise is where you store derived data and or duplicated data.

    but if you are recording a piece of data which is relevant and say forms part of a foreign key then its fine
    I do want the "Owner ID" field on the "Contract Form". It is very useful for th User to instantly see who owns the Property.

    But I do not know how to automatically populate the "Owner ID" in the "Contract Table"...if this is good practice?

    Cheers,

    Paul
    What would you attempt to do if you knew you would not fail?

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    My good practice is I name the pk the same name as the table name with ID ie

    Customer (table name)
    CustomerID
    Fname
    Snake
    ...
    ..

    Invoice(table)
    InvoiceID
    CustomerID
    Idate
    ...
    ...
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    By "enforcing your relationships" are you referring to "enforced referential integrity"??
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In my books there is stuff all point using a realtional database product such as Access/JET (or any SQL based db) and not enforcing realtionships.... its plain dumb as it allows bad data to be stored.

    so yes use enforce relational integrity options.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    Cheers for that.

    1. Have I created the correct Relationships??? (see attached screenshot 1)???

    2. I tried to create a one-to-many relationship between the ContractTable-Contract ID and the Rental IncomeTable-Contract ID but received the error message as per Screenshot 2.

    Eg. "No unique index found for the referenced field of the primary table" But, as you can see, I have Contract ID on both Tables???
    Attached Thumbnails Attached Thumbnails Relationships 1.jpg   Relationship 2.jpg  
    What would you attempt to do if you knew you would not fail?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as regards your error message...
    what do you think its trying to tell you?
    hint: whats different between contract table and the other 4 tables?

    I think you need to do some reading up or database normalisation. its one of the principals of (relational) database design.

    At present I don't think your design is normalised (the giveaways are numerous but ownerID1, Owner ID2 is one. what happens if there is more than two owners?)

    the tabel for rental income doesn't feel right logically to me
    rental is payable on a specific property contract on specific dates
    so to me the rental income should have the contract id , the date the payment was made and other stuff pertinent to the payment. it doesn't need the owner ID, the renter id or even the property id..
    why? because these are all available from the contract table basedon the foreign key contract id.. remember we don't store derived data.


    incidentally at this stage I'd avoid using spaces in table and column names
    I'd also drop the 'table' suffix on your table names.. why? its irrelevant, possibly confusing and will make your SQL harder to read and longer to type.

    there are those who would argue that owners and renters are essentially the same entity (effectively they are all people or organisations which could be either / or / both owners and renters of property and as we want to avoid duplication of data we should store common data in a common entity
    why? what happens if you store say a contact number. ideally you want to store the same information once. eg say you have the persons mobile / cell phone number you should store that snippet of information once so you only need to change it once, you only have one instance of that data there is no 'which is the right number to use'

    one of the potential issues is the treatment of rental income
    how do you know whether there is rental outstanding
    how do you cater for shortfalls in payments
    do you, say, create a negative amount int he rental income to crearte an invocie

    eg
    01/04/2011 rent demanded 650
    03/04/2011 rent recieved 600
    18/04/2011 rent recieved 50
    01/05/2011 rent demanded 650
    06/05/2011 rent recieved 600
    01/06/2011 rent demanded 650
    ...outstanding balance 700 June rent 650 + arrears of 50 from May
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by reddevil1 View Post
    I would like to get the design correct before I go any further, so i will read up on enforcing relationships - I hope this will help in this instance?
    It's basically just a matter of double clicking your relationship lines and then selecting "Enforce Referential Integrity".

    I am terrible with my knowledge of relationships however, i will try and play about with the 'drag and drop' wizard in Access and see if this helps. Wish me luck
    You won't find anything about M:M relationships in the wizard. You have to study them at the theoretical level first. A M:M results in two 1:M relationships in the end. But I think your situation needs a M:M to be in place so you'll have to get your head around them before going any further.

    My thinking was to make the Contract Table a 'master table' where I could easily find my four main fields all in one Table (eg. Property ID, Owner ID, Renter ID and Contract ID). Then i coudl easily find the information for my many, many reports that i am ready to create?
    I don't know the specifics of your business situation, so I can't be too helpful on that, but my first thought was that it is a bad idea to put fields in a table just so that it makes it easier to reference them later. Better is to get your understanding of relationships under control and put the fields where they belong, not where you want them!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Nov 2009
    Posts
    223
    Healdem,

    Thank you very much for your terrific reply and suggestions.

    as regards your error message...
    what do you think its trying to tell you?
    oops! i forgot to give the ContractTable a Primary Key - I have amended it now. I have also reseached the first 3 forms of normalisation and THINK my database now follows those rules.....unless someone thinks differently?


    At present I don't think your design is normalised (the giveaways are numerous but ownerID1, Owner ID2 is one.
    I had inserted Owner ID2 to help me overcome my original problem of populated the OwnerID in the Contracts Table. I have removed it now.


    the table for rental income doesn't feel right logically to me
    rental is payable on a specific property contract on specific dates
    so to me the rental income should have the contract id , the date the payment was made and other stuff pertinent to the payment. it doesn't need the owner ID, the renter id or even the property id..
    why? because these are all available from the contract table basedon the foreign key contract id.. remember we don't store derived data.
    Thanks especially for this comment. You are right, the Rental Income only needs the Contract ID. However, the reason I inserted the other columns of the other "IDs" was to help the User visually check that the Contract ID he had selected on the Form was indeed the correct ContractID (eg. by visually confirming the PropertyID, RenterID and OwnerID on the same Form). I am not sure how else I could easily "link" this important information and visually show it on the Form after the ContractID had been selected byu the User?


    one of the potential issues is the treatment of rental income
    how do you know whether there is rental outstanding
    how do you cater for shortfalls in payments
    do you, say, create a negative amount in the rental income to create an invoice
    Thanks again for being pro-active in your thinking and understanding my situation. I was going to assess this situation at the Report stage coz my thinking was that it coudl wait until later? Maybe my design needs to include something now, rather than wait unti later? Did you have anything particular in mind?
    What would you attempt to do if you knew you would not fail?

  13. #13
    Join Date
    Nov 2009
    Posts
    223
    StarTrekker,

    Thank you for your reply and comments.

    It's basically just a matter of double clicking your relationship lines and then selecting "Enforce Referential Integrity".
    I have read up about enforced referntial integrity. I have added the Contract Table's Primary Key and clicked on the relationship lines. Now I guess all the records will update automatically if any changes are made?

    But I think your situation needs a M:M to be in place so you'll have to get your head around them before going any further.
    I admit i only have a very basic understanding about Relationships. I can only think that my situation needs one-to-many Realtionships?? But I may be wrong?? Are there any areas which you were thinking may need a many-to-many Realtionship instead?


    My first thought was that it is a bad idea to put fields in a table just so that it makes it easier to reference them later.
    This is a very important point and it goes back to my original problem:-

    The Contract Form/Table has 4 important fields:-
    Contract ID (autonumber generated)
    Renter ID (User selection from ComboBox)
    Property ID (User selection from ComboBox)
    Owner ID (autopopulated based on the User selection from Property ID - above)

    But how do I automatically populate this field and show it in the Contract Table?
    What would you attempt to do if you knew you would not fail?

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you need an ownerid and a property id?
    presumably a property is owned by a person / organisation.

    as the contract is for a specific property then you can derive the owner of the property from the property table.. however you need to be careful and work out if its possible for the same property to be owned by different people over time. ie say landlord 98709 sells the property to landlord 76586.

    if thats the case then you need to push the owner of the property into an intersection table. where there is effectively a many to many realtionship between owner and property. this intersection table would have the primary keys of the two parent tables (ownerid and propertyid) which would normally comprise the primary key plus any other data which was relevant to this intersection. in this instance the other relevant data is going to be something date related.. that could be date of purchase or probably more likely dateoffirstlisitng (the date this property owner first placed this property with you.

    my gut feel would be that ownerid & propertyid would be OK as a primary key unless an owner may sell a property and then repurchase afterwards. if that happens then you will need to add some other piece of data to make the intersection entity unique or consider using a surrogate key

    however its your data model, your design. there may well be good reasons to store the ownerid in the contract table. rules are there to observed unless they don't apply. you can argue that a contract entity may have legal implications and therefore the owner id should be stored in that entity.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2009
    Posts
    223
    Healdum,

    Thanks for your reply.

    why do you need an ownerid and a property id?
    My thinking was that an Owner may own more than one Property and I thought it best to keep in separate Tables to save duplication of the Owner details?

    you need to be careful and work out if its possible for the same property to be owned by different people over time
    Yes, thats a good point - a property will probably change Owner over time. To overcome that situation, my plan was to create a new Property ID if the Owner changed? This is a slight duplication, I know, but I figured that a property would not change ownership too often?
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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