Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier together if needed?


In the customers table I think you can add an extra column which would flag whether that customer is also a supplier. So you could have a generic customer table (or you can name it contacts table), with a column used as a supplier flag/indicator (could be numeric: 1 or 0 or char: y or n or even boolean: true or false)

enter image description here

  • And probably a good idea to add a history table so if the contact was originally a customer, became a customer and supplier then changed to just a supplier then historical records would still be valid – kevinsky Jun 19 at 14:34
  • Like this there is no link between customer and supplier we are just adding a flag. Notice that the customers and suppliers maybe added to different tables. – HmH Jun 19 at 17:36
  • @HmH - Think of them as "People" (or "Contacts", as VC suggested). The extra column says what role they perform. It could even be SET('customer', 'supplier'). Some other columns may be NULLable if they apply only to a customer, not a supplier (or vice versa). – Rick James Jun 19 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.