Create a conditional using the relational model

Asked

Viewed 275 times

2

I’m trying to create a conditional "or" just using the relational paradigm:

Having the tables pessoa, cliente, funcionario and considering that a person may be a client or an employee (a 1:1 ratio, not null), it would be correct to reference the table cliente and funcionario with the same foreign key from the table pessoa? Example:
exemplo
The foreign key fk_tipo reference Funcionario.pk_funcionario and Cliente.pk_cliente.
The only problem I found in this mode is that the table Cliente cannot have a primary key equal to the other primary key in the table Funcionario and vice versa. Then you would have to review the mode of generation of these keys.

Is there any other way to accomplish what I want by making the most of the relational model?

Ps.: is not duplicate of "A foreign key can reference more than one table?".

sql relational-model
  • 1

    I don’t understand what you want, could you give an example? By the way, if "every customer is a person" and "every employee is a person" you can have the table pessoa, normal, and in the tables cliente and funcionario the primary key is also a foreign key for the table pessoa. This guarantees oneness, but nay prevents against: a) the existence of a person who is neither client nor employee; b) the existence of a person who is both client and employee.

  • 1

    @mgibsonbr, so I put the foreign key in pessoa.

  • 1

    In this case, what is the difference between this question and the other linked? If you refer to having two foreign keys in pessoa - one for cliente and another to funcionario - and wants to ensure that one and only one of them is NULL, maybe you can do it using check (I can give an answer with example, if this is what you are looking for). Personally, I still consider my initial suggestion better - one way or another one of the tables will be without referential integrity, and I prefer it to be the basis.

  • 1

    P.S. According to the same article linked in Wikipedia, can not use the check to validate a single column - ensure that it is a foreign key for one or another table - because this type of restriction usually only validates data in the same row of the same table. And to reiterate, I don’t see the difference between that question and the other one that you say is "not duplicate".

  • 2

    The other question is about whether a foreign key can reference more than one table, here is about how it would be possible to create an "or" using relational. I think it was a little implied that I emphasized my attempt using a foreign key referencing two tables. I’ll try to adjust the question.

  • 1

    Okay, I think I get it now. If I may mention the motivation behind this question (even if it is "just because I’m curious to know") will help a lot - because in practice, I do not see what benefits this would bring in relation to my suggestion (which, I just saw now, is the same thing was suggested in the answer to the related question). Anyway, +1 to the question, and I’ll wait for someone with more knowledge to answer (in fact the restriction check doesn’t fit here).

  • 1

    The other question was really out of curiosity, I was trying to figure out new ways to use the relational model. This is more to try to solve problems by simple means and not fill the bank with procedures, functions or triggers.

  • 1

    You need both tables for what reason? Have a table pessoa with all attributes of funcionario and cliente, and two columns to indicate the type the entity can assume (is_cliente and is_funcionario) resolves the question of its application in my view.

  • 1

    @gmsantos, specification. All the repeated lines in the two tables would be placed in Pessoa, the remaining in their due scales.

  • 1

    That’s exactly what I suggested. The difference is that the unique attributes of each entity would be null depending on the type, and you wouldn’t have to joins unnecessary to obtain information from an Employee or Customer

  • 1

    For clarity: http://sqlfiddle.com/#! 2/5afb6d/3

  • Yes, I could, but what I aimed for in doing so was to avoid null columns. Besides that this would allow a person to be both client and employee at the same time, this is the reason for "or".

  • The best normalization is the answer to the linked question. I didn’t understand why it didn’t solve the whole relationship problem: you wouldn’t have null columns, nor repeated records, and a person could be either employee, or client, or both.

Show 8 more comments

1 answer

2

In fact, I believe the ideal would be the opposite. Have in the tables: employee, client a FK_PESSOA. In other words, every client is a person. Every employee is a person. A person can be a customer or an employee. It would be +/- like this, from what I understand:

[pessoa] id, nome, demais campos relativos à pessoa...
[cliente] id, id_pessoa, demais campos relativos a cliente...
[funcionario] id, id_pessoa, demais campos relativos a funcionário...

  • The problem is that "by accident", it would be possible to create a customer and employee at the same time.

  • Really... This way would need to control by the application not to occur. Or even by rules in the bank, as trigers, to validate before inserting.

  • So it is impossible to create an "or" using only conditional?

  • I don’t know a way to limit just by relationship, this way.

Browser other questions tagged sql relational-model

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