Database Design: Unique Billing Assocation

Question

Our company is in the process of redesigning an old database. We've come across an association we need to show but not sure the best way to handle. Our business logic is that our customers (called distributors) will always have payment terms. However, some of these accounts payment terms will be controlled by a separate distributor account (ex: think chain stores and all billing goes through a corporate account).

We've come up with a couple solutions and would like some feedback on which one is most viable, or if we should be handling this billing association a different way.

Solution #1

Distributors
-------------
DistributorId
Name
PaymentTermsId (Points to separate PaymentTerms table)

Since each account has payment terms they're reflected in the Distributors table. Then the association is done in a separate table

DistributorBillingAssociations
------------------------------
DistributorId
BillingDistributorId (points to DistibutorId of Distributors table)

Now you're relied on business logic to make sure the Payment Terms match the same as the billing account. The biggest issue I have with this solution is that the design isn't intuitive. Hard for future users to know the payment terms is driven by the billing associations table.

Solution # 2

Distributors
------------
DistributorId
Name
PaymentTermsId (nullable)
BillingAccountId (references DistributorId, also nullable)

If a distributor doesn't have it's own Payment Terms then the column is set to null and through a self reference you'll know to use the Payment Terms from the Distributor set as the billing account.

Any feedback or suggestions is welcome.

Answer 1

I would go for Solution #2 because I think its most logic:

if (PaymentTermsId=null) PaymentTerms=(PaymentTermsFor(BillingAccountId))
   else PaymenntTerms=PaymentTermsFor(PaymentTermsID)

Answer 2

I would set up a paymentTermsID for every payment entity be it an individual distributor or a conglomerate and then have a FK from the distributor table to the payment terms table. if there are many distributors with the same payment terms, they simply refer to the same record in the payment terms table. this avoids any potentially confusing ifnull logic in your data queries.

select * from Distributors
left join PaymentTerms
  on Distributors.paymentTermsID = PaymentTerms.PaymentTermsID

I have made a rough sketch on sqlfiddle, take a look and play around http://sqlfiddle.com/#!6/c4a30/1/0