"foreign key" multiplexing

Each form has its advantages and disadvantages, obviously. Decide which is the best I believe you should do in your application, provided you have knowledge base to be able to make that choice decision. I will present a recent case that I passed in the company.

I work in a sales system, one of our features is that the seller has a current account. Thus, if the seller wants to give a discount more than the limit certain product, it consumes this current account and closes the sale.

Such a request may By Chance be pending, so the figure of the supervisor enters. The supervisor roughly approves or disapproves of orders placed by sellers. In addition, in case the supervisor wants to make it easier for a seller to close a last-minute purchase, he can make a transfer from his current account to the seller's current account.

Both supervisor and seller are users of the system.

That said, the modeling is very similar image of generalization/specialization that you put. I have the Table USUARIO, CONTACORRENTE (yes, it was alljoint the original name of this table) with non-null foreign key for USUARIO, VENDEDOR with non-null foreign key for USUARIO and SUPERVISOR with non-null foreign key for USUARIO. SUPERVISOR and VENDEDOR also have their own codes that have no relation to the user code.

Well, everything was quiet until the following customization arrived:

The buyer (in our system called CLIENTE) can also have a current account. In compensation you cannot require that this client has user

My first reaction was to propose creating artificial users to keep this relationship intact. Can I say that she was not at all well accepted? It had to do with business requirements because we sell user to use the system and this my idea would mess up cool this control.

Okay, next alternative? Multiplex the foreign key.

Yes, bizarre, but practical. Solved our modeling problems. We proposed a migration of such luck that operations made in the old CONTACORRENTE (now transformed into view) affected only the image migrated in the table new CONTA_CORRENTE (yes, we are creative :+1:).

It is worth saying that we erred in migration trigger what would do this magic? We forgot to test inserts in batch, which generates crazy inconsistencies... but obviously we fixed this error" tested " in production

Multiplexing works as follows:

1. I have a column that will be the "logical foreign key"of some table;
2. I have another column that simply indicates which table the first column points to.

The idea came from wavelength-based multiplexing of the MPλS , where through the intrinsic properties of data transmission in optical fibers the system knows which destination to direct.

The parallel between light waves and table rows then stood:

1. both have multiplexer attributes (wavelength/multiplexer column);
2. both load data that differs from the differentiating attribute (amplitude and phase of the wave/the other columns of the tuple).

In general, the multiplexing I used to represent this relationship can be understood through the following scheme:

The Triangle is not a real entity, it is just the "point" where multiplexing occurs.

Generally speaking, we work with databases of occasional integrity. It makes several operations in SQL-Server easier and we have the advantage that we can have temporary inconsistent States (but not like the that the SQLite allows), so we don't worry about putting literal FOREIGN KEY and CHECK CONSTRAINTS tries in the bank. We leave it up to those who will populate the data to do this in a coherent way.

This was a real solution to a problem that happened. I'm not going to say that every problem of this kind you should go this way. Nor will I say to always run away from such a solution. Knowledge is power, use it wisely and thrift.

Multiple foreign keys (aka question Mode 1)

This type of binding can cause problems, especially if it is deliberately chosen in project creation to avoid certain types of constraints to avoid overheads (1 e 2).

I have a pesquisa that a vendedor will apply to clientes that meet certain conditions. In this case, the conditions can be:

1. customers who buy certain representations (for example, those who buy Herbalife)
2. customers belonging to the particular route of the seller
3. all customers of that seller
4. question applicable to sellers under the supervision of a particular supervisor
5. all customers of all sellers without exception

This can be modeled as follows:

APLICAÇÃO_PESQUISA:
  - CD_PESQUISA
  - CD_REPRESENTAÇÃO
  - CD_SUPERVISOR
  - CD_VENDEDOR
  - CD_SUPERVISOR

For the sake of speed for the latter case, a additional flag , a identifier of all , thus being the final structure:

APLICAÇÃO_PESQUISA:
  - CD_PESQUISA
  - CD_REPRESENTAÇÃO
  - CD_SUPERVISOR
  - CD_VENDEDOR
  - CD_SUPERVISOR
  - ID_TODOS

For the sake of avoiding overheads, it does not have the constraint where only one of these values can be non-null.

Well, due to this" loose restriction " very dependent on registration (and, even worse, if this information is imported from an external system, we don't have too much control over it). So what happens to who will consume this information?

At the beginning of the conversation, these constraints are not formally documented anywhere, so the poor soul who will take the scheme and try to extract some meaning from it will take into account that any subset can happen. Except ID_TODOS marked as true and someone not null, this can not.

So it turns out that the poor soul who made this query, out of ignorance, had to take into account:

1. if there is a representation of that seller and the customer at the same time, but the customer is not served by that seller in that representation, is the search application valid for that seller and that customer?
2. and if it is marked a seller and a supervisor who is not part of your supervisor chain nor the supervisors of your supervisor, should I treat it as a kind of OU or is the data invalid?

Multiple foreign keys, the most specific

Has cases in which one really wants that multiple foreign keys can connect or not to a certain structure, by design even purposeful. I will not go too far into the merit here of the structure and its foreign keys.

Imagine you have 7 foreign keys. CE0, CE1, CE2, CE3, CE4, CE5 and CE6. There are 2⁷-1=127 distinct ways that such a marriage can occur. So, how to decide which ways more specific?

We can say that each row of this table has an Associated "bitrate". We can represent as a 7-bit number as follows:

• if the column CE_i is non-null, the position bit i is worth 1
• if column CE_i is null, the position bit i is worth 0

Then the "artifical column" bitagem can present the value 0110111 (little endian ), which means:

• 0 for column CE0, hence null
• 1 for column CE1, so not null
• 1 for column CE2, so not null
• 0 for column CE3, therefore null
• 1 for column CE4, so not null
• 1 for column CE5, so not null
• 1 for column CE6, so not null

For a value of 64+32+16+4+2 = 118

On this bitagem, we can define an auxiliary table that associates bitagem with an arbitrary prioridade, and can then make any desired ordering as the "most specific".

Although this formulation recalls taxonomy 1, as it allows making multiple connections at the same time, it does not fit into this category.