Results 1 to 7 of 7

Design advice please for manufacturing database with a friendly eye cast over my initial thoughts

  1. #1
    Macawac1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    17

    Design advice please for manufacturing database with a friendly eye cast over my initial thoughts

    Hello all,

    Just starting out on building db to manage production/ordering etc. from initial customer enquiry through to delivery of finished product. Very very rusty with Access and dbs as I have only built a fairly simple one many many years ago (in fact my hubby seems to think it was on floppy but not sure I agree!!). After staring blankly at my new empty Access screen a few weeks ago I tentatively started. Weeks on........ having now read what feels like millions of forum threads, buying a book, doing some online tutorials and playing around with several rough play copies I am now ready to start in earnest!

    I am aware of the importance of getting the underlying structure and relationships correct in the beginning and so on this note could someone kindly cast an eye over the info below to see if I am on the right track?

    Customers can also be suppliers (and obviously vice versa). Customers can also be manufacturers - although normally not dealt with directly but through a supplier (who can also be a customer - or a manufacturer!). Manufacturers can ship directly to us although the products are bought through the supplier. Manufacturers can also be suppliers. We also sometimes ship directly to our customers' customers - who for want of a better description I am referring to as "third parties" for now. We can use designs provided by our customers or by their customers (third parties). We request technical information from manufacturers - but buy through the suppliers in most cases (but not necessarily all). Confused yet? Hee hee - I certainly was! I have so far come up with this:

    CompanyType: Customer, Supplier, Manufacturer.
    AddressType: Customer delivery, Third party delivery, Supply outlet. (Both these have the potential to grow - eg Accounts office address or whatever I find I need)

    Suppliers, manufacturers and customers are lumped together in one "companies" table as they share the same attributes at company level (ie one website - one set of notes relating to the company etc....) with a "company details" table that defines their various roles.


    Customers' customers (my "third parties" for now) have been put in a separate table to the rest as I could not figure out how to relate them to our customer if they were in the main "companies" table. (table referring to itself???)

    Click image for larger version. 

Name:	Relationships.png 
Views:	12 
Size:	32.6 KB 
ID:	31536

    This is only the beginning bit - which I wanted to get as right as I could before moving further on with my tables/relationships.



    Many thanks in advance for all constructive criticism!!

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,053
    not sure why you have a table for company details - would think those details would be in the companies table (otherwise a company could be added more than once). Also, don't see the point of the active field - the same is achieved if datemarkedinactive is null.

    I would put the third party data in the companies table, no reason why a table cannot link to itself - on the relationships form, just drag the companies table on a second time, it will be appended with _1.Then perhaps change the CompanyID field name (from third parties) to something like 'CustomerOf'. If this field is null, then the customer is a 'main' customer'.

    You may not need the company type table, there are only 3 options and very unlikely to have further options - so use a value list

  3. #3
    Macawac1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    17
    Hi Ajax,

    Thank you.

    I have the company details to identify the supplier, customer or manufacturer eg:

    Company 1 = Customer
    Company 1 = Supplier
    Company 1 = Manufacturer
    Company 2 = Customer (only)
    Company 3 = Supplier
    Company 3 = Manufacturer (but not customer!)

    I cannot figure out how else to do this? My limited logic tells me I need this definition for when I start adding other things eg: parts (who supplies it - who manufactures it - one part could be manufactured and supplied by the same company or manufactured by one but supplied by another - or even manufactured and supplied by the customer as we receive parts from customers to go into the products we are manufacturing for them) etc... My limited logic seems to be stuck on needing these defined before getting to the parts list stage. I can't get the picture out of my head of having the CompanyID from one master companies list appearing as different fields in a parts table- although now thinking on a bit maybe this is the right way to do it? Just name one field supplier and one manufacturer but use the CompanyID to select value from companies table?? (hee hee - I am so very much a beginner!!!)

    Thanks for the insight into a table referring to itself. And yes - makes much more sense for a value list (I have read a wealth of advice on various sites to steer clear of lookups on tables and so am banging in lookup tables everywhere!!) I am a bit of a numpty for not spotting the duplication with Active and DateMarkedInactive!

    Will go back to the drawing board

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,827
    You may get some ideas from this free generic data model from Barry Williams' site. Note that it is generic and may not have all info you need. You can add to/remove from as appropriate --it is meant as a starting point.
    Good luck.

  5. #5
    Macawac1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    17
    Thanks Orange - will take a look.

    I have just had a thought about keeping the company details separate from the main company info - in that a customer who also supplies parts to us may well stop buying from us at some point, but may continue to supply - therefore I will need the ability to show the end of the customer relationship with that company, but to maintain the supplier relationship. Am I making any sense? I have tried to stay away from separate supplier, customer and manufacturer tables as to my mind they are all "companies" and so share attributes?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,827
    Consider, you could have a table identifying all Companies; and in your model you can "reuse" the Company table to identify those that are "Suppliers", and/or those that are "Customers" and/or those that are "Manufacturers" ( or Exporters/Importers....). Look carefully at your business rules/facts to see what is appropriate for your set up.

    The analogy here is the use of a "reference table" such as ISO Country Code which identifies every Country. That table can be used to supply values to

    CountryOfOrigin
    CountryOfDestination
    ExportsTo
    ImportsFrom

    It's your tables and relationships and business facts that "glue" these together in your application.

  7. #7
    ArviLaanemets is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Apr 2017
    Posts
    310
    How to set up customer/client/producer info depends on complexity of your data. In bigger ERP systems usually are separate tables for customers, clients (and in your case producers).

    1. Usually there exist a different information set you need for every type;
    2. Sometimes, when the same client and customer is big company, it may have different credentials for purchase and sales operations.

    Some clients/customers may have many different sites they are operating from, or they have different stores for goods, or they operate with different currencies or bank credentials, etc. In such cases usually you need to register the same customer/client several times. When this is the case, then you are better of when you have an additional structured unique identifier (p.e. ClientCode and CustomerCode), which allows to group different clients/customers. Then you can have p.e. customer info in combo box searching for customer to to be ordered by CustomerCode and CustomerName, so you have all customers of same group listed together.

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

Similar Threads

  1. Replies: 7
    Last Post: 05-27-2015, 06:11 AM
  2. Advice to learn database design
    By cib in forum Database Design
    Replies: 2
    Last Post: 01-27-2014, 08:10 PM
  3. Advice on Database Design
    By JoeM in forum Database Design
    Replies: 1
    Last Post: 08-15-2013, 07:50 AM
  4. A Challenging Database Design for Manufacturing Process.
    By cap.zadi in forum Database Design
    Replies: 6
    Last Post: 11-10-2012, 10:31 AM
  5. Replies: 5
    Last Post: 04-02-2012, 07:24 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
  •  
Tech Forums: Microsoft Office Forums