3

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.

geez0r
  • 47
  • 4
  • This question looks like a request for code-writing service. In addition to the requirements definition, please include your SQL queries, sample data and highlight the problematic part. Best regards, – Alexander Bell Jun 10 '15 at 04:35
  • 1
    As a guide if the problem is on the whiteboard it is a conceptual question and should go to http://programmers.stackexchange.com/, if it's on the screen in code then it belongs here, preferably with the snippet of code you are stuck on. – Stephen Turner Jun 10 '15 at 07:55
  • 5
    I'd just like to note my disagreement with the previous comments - this is a database design question (which we have plenty of on SO, and a tag for), with two solutions the asker has already provided. It simply asks which to use in light of the described requirements, or whether they are missing another option - this is in line with many other acceptable database design questions here, and this one is quite clearly stated. I'll try to answer at lunch time or this evening if it's not already been answered by then. – Jo Douglass Jun 10 '15 at 09:43
  • @AlexBell - I'm not looking for any code writing, my intent was to get feedback and suggestions on the best way to handle this unique association purely from a database design point of view. – geez0r Jun 10 '15 at 13:21
  • If I understand you correctly, in solution 2, either the PaymentTermsID is null, or the BillingAccountID is null, but not both? If this is true, then solution 2 is more expressive. – Gilbert Le Blanc Jun 10 '15 at 15:11
  • @GilbertLeBlanc - Yes, you're understanding correctly. With solution 2 only one of the two could be null but not both. Thank you for the feedback. – geez0r Jun 10 '15 at 17:26

2 Answers2

0

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

if (PaymentTermsId=null) PaymentTerms=(PaymentTermsFor(BillingAccountId))
   else PaymenntTerms=PaymentTermsFor(PaymentTermsID)
olegaarde
  • 31
  • 3
0

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

Patrick
  • 416
  • 3
  • 7
  • The only problem with this solution is that we don't know who is controlling the payment terms for an individual distributor. This relationship needs to be shown if it exists because it will be part of future reporting needs. – geez0r Jun 11 '15 at 18:02