X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Design Of Customer/supplier Table, Access 2003    
post Aug 15 2019, 04:05 AM

Posts: 91
Joined: 14-September 06


A simple question i hope.

I am looking at having a table of both customers and suppliers. In previous databases i have separated them into two tables but i want to put then in the same table for various reasons.

So i thought just have a true/False field to differentiate, where say a true is a customer record and a false is a supplier.

Is this the best way or is there a gotcha in doing so?

Thanks Neil
Go to the top of the page
post Aug 15 2019, 05:16 AM

UtterAccess VIP
Posts: 6,900
Joined: 30-June 11

I wouldn't use a Yes/No field but rather a Numeric field with a related table.

Basically, I would create a table 'ContactTypes' with a PK 'ContactTypeId' and then use that same field as a FK in your Contacts table. This give you much more flexibility.

Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
post Aug 15 2019, 05:32 AM

Posts: 91
Joined: 14-September 06


Ah yes good call, I have that in many other table relationships so it will be easy to administer.

Go to the top of the page
post Aug 16 2019, 02:50 AM

Posts: 1,010
Joined: 26-January 14
From: London, UK

I also would use either a string or a number to represent the contact type.

There might be certain attributes which are specific to one type or another, i.e. attributes that don't apply to all types of contact. You could consider creating additional tables for each distinct type (subtype) and referencing your parent contact table with a foreign key from each of the subtype tables. The parent table would contain only the attributes that are common to all contacts. This kind of situation is known as a supertype/subtype pattern.
Go to the top of the page
post Aug 16 2019, 04:33 AM

Posts: 16
Joined: 26-September 18

Hi Neil

Is it possible that a "Supplier" is also a "Customer" of yours? They might supply you with products but also buy your services. If they change their address (or any details), you only want to change one record not many. Consequently you might want a table called "tblCompany" that has a field showing company_type. It could be Customer, Supplier or Both. There are other more complex solutions but that depends on your data.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th October 2019 - 09:48 PM