Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Question Unanswered: Single form displaying fields from multiple tables

    I'm trying to work on a database for tracking machinery. Each item of machinery has some fields that are the same regardless of machine type (purchase date, invoice number, purchased by etc) and then each asset 'type' has several fields specific to that type of machine (e.g. a machine with tracks will have different information recorded to a machine with wheels). What is the best way to store this in a database? I've read up on data normalisation and good design but am still unsure.

    As far as I can work out I have three options.
    1. One table that contains all machinery fields (any fields that don't relate to the machine type being entered will be left null)
    2. A separate table for each asset type (e.g. roller table, excavator table, tractor table)
    3. A 'master' machinery table that records details that apply to all machines and then separate tables that record details that are specific to the type of machine. Each piece of machinery would have one record in the master table and then a linked record in the relevant machine specific table.


    From a normalisation point of view the last option appears the best however I don't know how to get this to work on a form. Is it possible to have information from multiple tables on one form so that adding a new record using the form would add a new record to the 'Machinery' data table as well as to the machine specific table?

    I'm using access 2010 and I'm quite happy to do more research if it's required... I just need someone to point me in the right direction... any advice or hyper-links would be much appreciated.

    Regards,
    Matt.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Look at (what i find the confusingly called) sub / super type model.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2014
    Posts
    3
    Thanks for the prompt response. I've done some quick reading (most of which went over my head ) but that is definitely what I was after. Have you any suggestions on where I can find information on how to implement this on an access form?

    Would I have to use sub forms or can I add the fields onto one form and show/hide them based on the machine type?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In the access world you are tied to forms and reports, using sub forms / reports as or if required. So the common stuff goes in the parent table. The stuff pertinent to each category goes into sub forms.
    How you handle the sub form is upto you
    You could have all sub forms each on a separate tab
    You could load the required subform once the user defines the specific type of supertype
    You could have a separate master and subform for each supertype (not a design id be happy with personally).
    One thing to bear in mind with sub/supertype model is housekeeping. If a userstarts defining data for a tracked vehicle, then abandons that for a wheeled vehicle then you need to decide what to do.. do you delete the tracked vehicle data....... as you can have only one supertype youve got keep only one set of data. It could be a user error they mistakenly selected a different supertype and need to retain the data, or they should have used the other supertype form. You have to enforce the one supertype rule yourself.

    Be wary of too many levels of sub/supertype. It can be tricky. To model many levels. If you dont need need a rigourus one column per attribute then an eav model may work for some attributes but if you ned to capture a specific attribute then eav is a pita. Ive used hybrid designs where largely irrelevant stuff was stored in eav
    Eg comments, descriptions reviews. Eav is fine if its not critical data but its an academics answer to database issues and it has serious problems in the real world.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2014
    Posts
    3
    Thank you for the detailed reply... very much appreciated. I've got some more homework to do before I can decide but I'll probably go with the tabs method...

Tags for this Thread

Posting Permissions

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