Is it wrong to leave an FK inside the table that originates the PK?

Question

Is it wrong to leave an FK inside the table that originates the PK?

Navigation

#1 by (5 votes)

2

Is it wrong to do this on a bank table?

I'll cite an example:

We have the Client: Google And in it we have the unit: Google Brazil

Then we have the following table example: Client Table

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 a Unit of some array

Is it wrong to do this? It's good? what pros and cons to make this structure?

sql

asked by anonymous 17.10.2014 / 15:41

1 answer

Difference between null, empty, 0 and false by picking up the record in the database How to know when two threads have ended in Swing?

score 5 · Accepted Answer

No, it's not wrong.

If Google and Google Brazil are the same type of entity (both are clients), it is natural to persist in the same table.

If a client can have an array or branches, and the array and each branch are a distinct client, it is natural that they all remain in the client table.

If you intend to relate a branch customer to the parent account of this branch, this foreign key is pointing to the same table.

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

Now consider another scenario:

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

It's still 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 rename the table because a table called "Clients" should only store clients. You could call it for example "People" or "Legal People" or "Partners" or whatever would make the most sense for your domain.

At the time this table has multiple fields unique to the client itself or multiple fields unique to the units, you should refactor in more than one table.

As for performance, you need to start from a known problem, or even speculation, but that can be worked out as a well-defined problem. So you do some studies to prove and solve this problem. Do not assume non-obvious performance issues that you can not prove from 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 as long as there is, so to speak, a common type in the hierarchy above those tables. I am speaking conceptually, eg "Matrix" and "Units" are different entities in the domain, but an understanding of a common entity is possible - both are "Legal entities" with which you have contact.

The following are suggestions and someone can find good reasons to the contrary, although I myself do not visualize them:

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

The downside of not following these suggestions is that you end up generating a table with many columns where many of them do not make sense to all records. In this situation, the understanding of the table is impaired and redundant fields or fields with more than one objective begin to emerge, further damaging the understanding, throwing you into a vicious cycle of increased complexity.