Results 1 to 6 of 6
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Companies / People / Customers

    Hi all,



    I am currently trying to think of a best way to design the following:

    • We are mostly B2B (we sell to companies)
    • We also have companies as suppliers
    • Both customer companies and supplier companies have multiple contact persons
    • A company can be BOTH customer and supplier to us
    • Sometimes the customer is actually a person, not a company


    So far I'm working with a typical setup, tables:
    • Customers
    • Suppliers
    • People


    But thinking about it, this won't work in my case. What do you think would be the solution here?

    My idea:
    • tblCompanies
    • tblCompanyTypes (M:N relationship with tblCompanies)
    • tblPeople
      • FK: CompanyID (not required)
      • Field: IsBusinessUnit (yes/no)


    Then there would be a query:
    • qryCustomers (list of all companies where CompanyType is Customer, UNION with People where IsBusinessUnit is true


    Does this make sense?

    Thanks.

    Tomas

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I don't think there is a correct answer to this. I have seen both approaches used, and personally I don't know what is best.
    In a large organisation it seems sensible to keep the two sides of the business separate, if it means duplication then so be it, but your accounts package (assuming you have one in a larger business) will probably end up being the steering force here.

    In a large organisation your purchasing department probably don't care about selling stuff and vice versa.
    In a small business keeping it together might make more sense.

    And yes your customer could be Mr Smith, but he'll still need handling.
    One off customers we used to store as a delivery address under a general account of CashSale01.
    That way it didn't clutter up our regular customer DB and we could easily search by postcode/name under the Delivery addresses.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by Minty View Post
    I don't think there is a correct answer to this. I have seen both approaches used, and personally I don't know what is best.
    In a large organisation it seems sensible to keep the two sides of the business separate, if it means duplication then so be it, but your accounts package (assuming you have one in a larger business) will probably end up being the steering force here.

    In a large organisation your purchasing department probably don't care about selling stuff and vice versa.
    In a small business keeping it together might make more sense.

    And yes your customer could be Mr Smith, but he'll still need handling.
    One off customers we used to store as a delivery address under a general account of CashSale01.
    That way it didn't clutter up our regular customer DB and we could easily search by postcode/name under the Delivery addresses.
    If both approaches are considered a good practice, then I would incline to the original design (Customers, Suppliers, People separated).

    We are a small business (think of approximately 1000 customers, 10 suppliers - 2 of which are dominant). Probably around 98 % of customers are actually businesses. So duplicities are rare.

    I think it'll be the easiest for me, what do you think?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Don't think you necessarily need a company type flag if you have other information tied to the company such as sales invoices or purchase orders. If you need to know the type, a simple left join to each (or use the Exists operator) would be sufficient. Only exception might be a new company with no associated records. But you may need to flag your contacts with company type so you don't email the supplier part of the business with sales literature for example.

    With regards 'general' contacts not belonging to a company, suggest include them as a company anyway (of have a company called 'individuals' or similar) otherwise you cannot maintain referential integrity. Alternatively, not sure what isbusinessunit represents but if to differentiate between company and individual then think you need to turn this upside down.

    People becomes the 'primary' table
    companies becomes the 'child' table
    then the company type would be linked to companies

    It doesn't really change the relationships - just the way you look at them

    Finally, as described it is a bit simplistic - what about when a person moved from one company to another, Or from customer to supplier parts of the business? Or a company stops being a supplier but remains a customer or variations thereof. So think you need to use date fields rather than yes/no flags to show 'when' this happened

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    follow on - with more info provided, think best to keep separated. You can always crate a joining table to join customer and supplier records together when they are the same organisation (or include a customer field in the supplier table) if you want to track that relationship

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Just another option - we have the many to many setup:
    Companies (compID, compName, compNumber, ...) - CompaniesCompTypes (cctID, cctCompanyID, cctCompTypeID, cctDateFrom, cctDateTo) - CompTypes (ctID, ctDescription)

    Companytypes are: Supplier, Customer, Transporter, SalesProspect

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

Similar Threads

  1. Replies: 3
    Last Post: 08-18-2017, 07:26 AM
  2. Adding people to case table from people table.
    By fuonge in forum Database Design
    Replies: 2
    Last Post: 09-22-2015, 11:53 AM
  3. Replies: 1
    Last Post: 09-09-2014, 11:29 PM
  4. Replies: 1
    Last Post: 08-06-2014, 02:22 PM
  5. Replies: 0
    Last Post: 12-28-2009, 12:14 PM

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