Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Tables + Relationships _ Design Of Customer/supplier Table

Posted by: Paxman Aug 15 2019, 04:05 AM

Hi

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

Posted by: DanielPineault Aug 15 2019, 05:16 AM

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.

Posted by: Paxman Aug 15 2019, 05:32 AM

Daniel

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

Thanks

Posted by: nvogel Aug 16 2019, 02:50 AM

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.

Posted by: RayFrew Aug 16 2019, 04:33 AM

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.

Ray