Best practice sql server - relation between tables that may or may not be retated

Question

4.00/5 (1 vote)

See more:

Consider that I have 2 tables: Purchases and payments.
Purchase
idPurchase
date
vendor
...

Payment
idPayment
value
date
...

For all my purchases I am eventually going to add some payments, but there are some payments that do not refer to any purchases, like a payment to an employee.

Should I add the foreign key idPurchase to my Payment table and leave it as null on this cases or is it better to use an external relation using a new table having two foreign keys like:

Rel_Purchase_Payments
idPurshase
idPayment

What is the best practice and which gives me the best performance and scalability?

Posted 27-Aug-15 9:16am

Felipe C. Vieira

Add a Solution

Comments

ZurdoDev 27-Aug-15 15:38pm

Performance will depend on how many rows you end up having and the indexing you use, not necessarily which table method you choose.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2015-08-27T09:39:00

Solution 1

This is more about performance and integrity, much less about scalability.

As payment appears to be more fundamental than purchase, it would be reasonable to do just the opposite: have the foreign key pointing to payment in purchase table. Even if you have some purchase without payment (bonus, free gift, anything), you still can use your null.

Having the back reference from purchase to payment (to be able to quickly find the payment by some purchase) may not really help you. Here is why: for better flexibility, you should not assume pure injective function from purchase to payment; this should really be the many-to-many relationship. Indeed, some purchase can be payed off in more than one payment; and some payment can be done to cover more than one purchase. So, ultimately, develop the database scheme with many-to-many relationship between the two. Please see: https://en.wikipedia.org/wiki/Many-to-many_%28data_model%29[^].

—SA

Posted 27-Aug-15 9:39am

Sergey Alexandrovich Kryukov

Comments

Felipe C. Vieira 27-Aug-15 16:03pm

Hello Sergey,
I understand that this is probably a many-to-many situation, but I am more concerned about the best practices in cases where a relation may or may not exist.
Imagine I am only going to use payments for a single purchases (many payments - 1 purchase), but I can also pay a payroll (many payments - 1 payroll).
In this case i could use the foregn keys idPurchase and idPayroll in my payments table, but one of then would be set as null.
On the other hand, I could use external relation for the two possibilities and have no nulls on the payment table. This way I dont have to change the payments table if later on I find out other kinds of situations that may need payments.
The problem is that this can lead to a lot of tables to set the relations and may jeopardise my performance.
For as I see, I may gain on scalability, but I am going to lose performance. Is that right?

Sergey Alexandrovich Kryukov 27-Aug-15 16:43pm

There is no problem with using null. Your considerations look reasonable to me.
Performance... you can have problem with performance if, say, the query would have to search all the purchases to find related payment, but what choice would you have. And introduction of many-to-many could give you reasonable performance.
—SA

Wendelius 27-Aug-15 23:57pm

Good answer!

Sergey Alexandrovich Kryukov 28-Aug-15 0:23am

Thank you, Mika.
—SA

Maciej Los 28-Aug-15 3:07am

5ed!

Sergey Alexandrovich Kryukov 28-Aug-15 9:25am

Thank you, Maciej.
—SA

Maciej Los · Accepted Answer · 2015-08-27T11:19:00

Solution 2

This question is not about performance and scalability but about design issues and database normalization[^] which may affect on performance!

None of your approaches is good.

Possible solutions:
1) (recommended)
I'd suggest to create 2 different "payment" tables. One for purchases and second for employee payments.
Purchase_payments

Payment_ID
Purchase_ID

and

Employee_payments

Payment_ID
Employee_ID

2)
I saw somewhere another solution, but i hadn't possibility to test it.
Common_payments

Payment_ID
Related_ID
Related_Table

Where Related_Table column stores the name of related table.
Note: This involves you to use dynamic queries[^].

For further information, please see:
Database normalization[^]
Normalization[^]
Database Normalization Basics[^]
SQL by Design: Why You Need Database Normalization[^]

Posted 27-Aug-15 11:19am

Maciej Los

Comments

Sergey Alexandrovich Kryukov 27-Aug-15 19:35pm

Not a bad idea and explanation, but I voted 4 this time, because it would be more specialized (payment for this, for that) than it really has to be.
—SA

Maciej Los 28-Aug-15 3:07am

Thank you, Sergey.

Wendelius 27-Aug-15 23:56pm

Nice answer!

Maciej Los 28-Aug-15 3:07am

Thank you, Mika.

Felipe C. Vieira 28-Aug-15 10:48am

Hi Maciej,
I tried the dynamic query, but there is a problem when i have to show all payment records and its sources (payrolls and purchases). I couldnt find a way to do a dynamic join (i dont think it is possible), so to use the dynamic query I would have to dynamically write all the possible queries and then execute an union for the results.
It would be to much trouble to do an apparently simple task.
The first solution is also not really good for me because the treatment given to all payment types is the same on the business logic.

Maciej Los 28-Aug-15 10:55am

OK, as i wrote, second solution i saw somewhere but never used. So, try Sergey's solution.