none
Table Design - Material per scaffold RRS feed

  • Question

  • Hi Everyone, 

    I'm relatively new to Database design and I am struggling with putting this type of table together. 

    I will be pulling a CSV from AutoCAD that is going to be grabbed by Access VBA, but what I am looking for is:

    - Access to store this information in a table that holds all of the material from the CSV associated to one ID in one line

    I am getting tripped up because:

    -Either I am to build a table that has every possible component listed, which could get messy when adding or modifying that list.

    -Or I many-many relationship that will be huge table with a ScaffoldID, MaterialID, MaterialQuantity in every line row, and some of the scaffolds have 50-60 types of Material.   

    I will have pictures if i am not explaining myself well enough, after my account is verified. 

    Any help would be greatly appreciated


    -Matt 

    Friday, April 7, 2017 3:46 PM

Answers

  • -Or I many-many relationship that will be huge table with a ScaffoldID, MaterialID, MaterialQuantity in every line row, and some of the scaffolds have 50-60 types of Material.   

    I would think you need three tables --

    tblScaffold -

    ScaffoldID - primary key

    DesignDate

    Project

    DesignBy

    ApproveDate

    ApproveBy

     

    tblMaterial -

    MaterialID - primary key

    Description

     

    tblScaffoldMaterial -

    ScaffoldMaterialID - primary key

    ScaffoldID – foreign key

    MaterialID – foreign key


    Build a little, test a little

    • Marked as answer by PEIMatt Monday, April 10, 2017 6:18 PM
    Friday, April 7, 2017 5:25 PM

All replies

  • Hi Matt,

    It would also help to see what a CSV might look like, but the second option sounds to be the correct way to approach this.

    Just my 2 cents...

    Friday, April 7, 2017 3:52 PM
  • -Or I many-many relationship that will be huge table with a ScaffoldID, MaterialID, MaterialQuantity in every line row, and some of the scaffolds have 50-60 types of Material.   

    I would think you need three tables --

    tblScaffold -

    ScaffoldID - primary key

    DesignDate

    Project

    DesignBy

    ApproveDate

    ApproveBy

     

    tblMaterial -

    MaterialID - primary key

    Description

     

    tblScaffoldMaterial -

    ScaffoldMaterialID - primary key

    ScaffoldID – foreign key

    MaterialID – foreign key


    Build a little, test a little

    • Marked as answer by PEIMatt Monday, April 10, 2017 6:18 PM
    Friday, April 7, 2017 5:25 PM
  • -Or I many-many relationship that will be huge table with a ScaffoldID, MaterialID, MaterialQuantity in every line row, and some of the scaffolds have 50-60 types of Material.
    Far from being 'huge' that sounds quite modest in relational database terms.  Say the referenced Scaffolds table has 1,000 rows the table which models the binary relationship type between it and the materials table would have a maximum of 60,000 rows.  That is not a lot in this sort of model.

    Ken Sheridan, Stafford, England

    Saturday, April 8, 2017 10:54 PM
  • 60,000 rows per site and there will 100's of sites a year, wouldnt that be getting close to a maximum or some sort of slow down within the Database?

    Monday, April 10, 2017 3:14 PM
  • The 60,000 figure was a hypothetical one.  It would assume 1,000 rows in Scaffolds per site with each row referenced by 60 rows in the table which models the relationship type between scaffolds and materials.  Even if that is realistic, performance would be governed more by good indexing than by size, and you could use Access as the front end to an SQL Server back end if the size of the back end were likely to approach the 2 GB limit for an Access file.

    The real point here is that the appropriate model is a three-table one such as that described by Karl, though his omitted a Quantity attribute.

    Ken Sheridan, Stafford, England

    Monday, April 10, 2017 4:07 PM
  • I think I've come to the same conclusion as Karl.

    What about using a CSV. Files

    ScaffoldID - Compound Primary Key
    CSVID - Compound Primary Key

    CSVID - Primary Key
    CSVInfo - Long Text

    Use VBA to seperate for Queries? and keep the data stored together when not needed  

    the reason I am thinking CSV, is that will be the output of another program I will be using. And wouldnt that keep the overall memory usage lower?

    Monday, April 10, 2017 6:18 PM