Is it wrong to leave a FK inside the table that gives rise to PK?

Question

Is it wrong to leave a FK inside the table that gives rise to PK?

Asked 9 years, 11 months ago

Viewed 180 times

2

It’s wrong to do this on a bank table?

Let me quote an example:

We have the Customer: Google And in it we have the unit: Google Brasil

Then we have the following example table: Table Client

Id | Nome          | Matriz 
1  | Google        | null

Id | Nome          | Matriz
2  | Google Brasil |  1

That is, I left, my Client and Unit all in the same table, leaving only one Matrix column to differentiate whether or not it is a Unit of some matrix

Is it wrong to do this? Is it good? What pros and cons in making this structure?

It depends on the purpose of this table. If you have a simple hierarchy, this approach will make it easier to construct selects without joins, but you get tied up in this simple hierarchy (No n-n relation allowed)

– gmsantos

2014/10/17 at 13:49
I couldn’t imagine where you wouldn’t let me have N-N

– Rod

2014/10/17 at 14:01
n-n - many to many. Google Brasil could not be one level below Google USA and Google Singapore at the same time in this modeling. (I took only as an example.)

– gmsantos

2014/10/17 at 14:06
It is difficult that a company unit has more than one head office, it says right, head office, single, rsrsrs, currently I leave it in a separate table, but I thought that it can give me a performance increase, and also, a facility, since in the code, I leave as composition only the "Client"and in it I already know if the data goes to the matrix or unit

– Rod

2014/10/17 at 14:10
@Caffé what he meant without the joins was that to get all the affiliations of company X, just one WHERE matriz = X

– gmsantos

2014/10/17 at 14:12
As for the performance increase, will get doing so?

– Rod

2014/10/17 at 14:13
@Rod but already chose the correct answer ?

– gmsantos

2014/10/17 at 14:16
1

gmsantos, both helped in the answer, although my biggest doubts were here in the comments, if you want to elaborate a more detailed answer I change as I think it is more elaborate and more detailed for other users see

– Rod

2014/10/17 at 14:18
1

While it is a right to choose an answer whenever you want, I think you can get more useful information if you leave the question open for a while. cc @gmsantos

– Caffé

2014/10/17 at 14:19
Yes, you can choose the answer at the time you understand Rod, but it may discourage them from including new answers.

– gmsantos

2014/10/17 at 14:24
I’ve already edited, I’ll leave it open for now

– Rod

2014/10/17 at 14:26
@Rod the name might even be 'matrix' and have a greater significance in the real world, but in matrix data domain would just be a company without... matrix. =)

– OnoSendai

2014/10/17 at 15:11

Show 7 more comments

1 answer

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.

by Caffé • **17,429** points · Answer 1 · 2014-10-17T14:03:45+00:00

No, it’s not wrong.

If Google and Google Brasil are the same type of entity (both are customers), it is natural that they persist in the same table.

If a customer can have a head office or branch office, and the head office and each branch office are a distinct customer, it is natural that they are all in the customer table.

If you intend to relate an affiliate client to the parent client of this affiliate, it is natural this foreign key pointing to the same table.

Considering only what you have exposed, there are no negative points in the modeling of this table.

Let us now consider another scenario:

Google and Google Brasil are not both customers. Only Google, the parent company, is a customer (for example, you only invoice against Google Matrix). Google Brasil is an affiliate that you need to know for some business demand, but it is not your customer. In this case, it is wrong to leave headquarters and branch in the same table?

Answer: Still it is not wrong that they are in the same table if the table stores basically the same attributes (CNPJ, Address, Phone...).

But in this case you would have to change the table name because a table called "Customers" should only store customers. You could call it "People" or "Personal" or "Partners" or whatever makes more sense to your domain.

Once this table had multiple fields unique to the client itself or multiple fields unique to the units, you should refactor to more than one table.

As for performance, you need to start from a known problem, or even speculated but that can be elaborated as a well-defined problem. So you do some studies to prove and solve this problem. Don’t assume any obvious performance issues that you can’t prove by your experience or testing.

Concluding:

Name the table consistently with your records.
It is okay to keep entities of different types in the domain in the same table if they have the same attributes and since there is, so to speak, a common type in the hierarchy above these tables. I am speaking conceptually, example: "Matrix" and "Units" are different entities in the domain, but it is possible to understand a common entity - both are "Legal Persons" with which you have contact.

The following are suggestions and someone may find good reasons otherwise, although I do not visualize them myself:

Store in separate entities tables that have nothing in common other than the names of some attributes.
Store in separate tables entities that have something in common but have more differences than similarities.
Aim to develop the software so that it has a reduced impact when refactoring the database - so you can evolve the design of the database throughout the system lifecycle.

The downside of not following these suggestions is that you end up generating a table with many columns where many do not make sense for all records. In this situation, the understanding of the table is impaired and redundant fields start to appear or fields with more than one goal, further damaging the understanding, throwing you in a vicious cycle of increasing complexity.