Results 1 to 5 of 5
  1. #1
    mcgarr423 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2016
    Posts
    10

    Fabrication shop data model (trying not to reinvent the wheel here)

    I've been maintaining a database for a metal fabrication shop, but it was started from scratch by someone without enough knowledge to start a database from scratch (myself). As changes are made to the database, small problems spiral out because of the poor foundation of the data model... at this point I would like to start building a new database with a solid data model.



    The basic need of the database is to create/track the progress of projects/orders, create/track quotes requested by our customers, and create/track invoices based for finished projects.

    The orders are for custom parts (new part# designation), catalog parts (existing part#), or repair service. I have attached a screenshot of the existing relationships/tables. Again, not looking to fix this database, just a new data model that I could modify to fit my purposes and import data from the previous database.

    Click image for larger version. 

Name:	Relationships.Tables.png 
Views:	188 
Size:	151.3 KB 
ID:	26783

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    without knowing your processes etc difficult to say what is fit for your purposes. For example are jobs always quoted or do you have jobs which have not been quoted? If always quoted the relationship would be customers>quotes>jobs

    I can say you need to work on your Quotes table which should be split in a similar manner to your invoice table and will have a knock on effect on the relationship between the parts and job lines table.

  3. #3
    madroamer is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    4
    hi

    can u share ur db to understand the model

    Quote Originally Posted by mcgarr423 View Post
    I've been maintaining a database for a metal fabrication shop, but it was started from scratch by someone without enough knowledge to start a database from scratch (myself). As changes are made to the database, small problems spiral out because of the poor foundation of the data model... at this point I would like to start building a new database with a solid data model.

    The basic need of the database is to create/track the progress of projects/orders, create/track quotes requested by our customers, and create/track invoices based for finished projects.

    The orders are for custom parts (new part# designation), catalog parts (existing part#), or repair service. I have attached a screenshot of the existing relationships/tables. Again, not looking to fix this database, just a new data model that I could modify to fit my purposes and import data from the previous database.

    Click image for larger version. 

Name:	Relationships.Tables.png 
Views:	188 
Size:	151.3 KB 
ID:	26783

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you just need to apply your Job Model to your quote.
    In fact if you got smart with it your quote could become you job record by simply adding a PO or something similar.
    E.g Your quote is in the same format as your Job, or at least should be, as presumably most of the quote information becomes the Job info.

    So if you added a customer purchase order to your quote header, that has just turned into your job header... Food for thought?

    It might be worth separating out the customers headline address, and add a shipping address table linked back to the customer ID - what if they want it shipped elsewhere? May not apply to your business model.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.


    Autonumbers--What they are NOT and What They Are
    Microsoft Access Tables: Primary Key Tips and Techniques


    You need to understand Normalization.
    Look at your table "Quotes". It has repeating fields:
    PartLine1, QtyLine1, DescriptionLine1
    .
    .
    .
    PartLine10, QtyLine10, DescriptionLine10


    These should be in another table (maybe "QuoteDetails"); similar to "Invoices" and "Invoice Lines".
    I might think about combining tables "Quotes" and "Jobs"; moving some fields from the "Quotes" table to the "Jobs" table and changing the "Quotes" table to a details table ("JobDetails ??)". (would need to know more about your project)


    I use a suffix for the PK/FK fields. Example "Customers.CustomerID_PK" and "Jobs.Customer_FK". For me, having "ID" in the name means the field is a number type and if it is a PK field, it is an autonumber type.


    Just curious.... In table "Timesheets", there is a field "Employee", but I do not see a table of employees.




    Here are links for further reading:

    What is Normalization?
    What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
    What Is Normalization, Part II: Break it up http://rogersaccessblog.blogspot.com...n-part-ii.html
    What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
    What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
    What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html


    Entity-Relationship Diagramming
    ntity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html
    Entity-Relationship Diagramming: Part II http://rogersaccessblog.blogspot.com...g-part-ii.html
    Entity-Relationship Diagramming: Part III http://rogersaccessblog.blogspot.com...ming-part.html
    Entity-Relationship Diagramming: Part IV http://rogersaccessblog.blogspot.com...-i-ii-and.html


    The Normal Forms
    The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html
    The Normal Forms: First Normal Form (1NF) http://rogersaccessblog.blogspot.com...-form-1nf.html
    The Normal Forms: Second Normal Form (2NF) http://rogersaccessblog.blogspot.com...nd-normal-form
    The Normal Forms: Third Normal Form (3NF) http://rogersaccessblog.blogspot.com...rmal-form.html
    The Normal Forms: In a Nutshell http://rogersaccessblog.blogspot.com...-nutshell.html




    You can post your dB if you want a review of the table structure/relationships.....

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

Similar Threads

  1. Help with data model/er diagram
    By Beanie_d83 in forum Database Design
    Replies: 4
    Last Post: 06-22-2016, 07:25 AM
  2. Bill of Materials Data Model
    By uaguy3005 in forum Database Design
    Replies: 3
    Last Post: 12-21-2015, 02:56 PM
  3. Replies: 1
    Last Post: 03-03-2014, 04:08 PM
  4. Replies: 1
    Last Post: 10-28-2012, 02:54 PM
  5. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 AM

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