What SQL relationship suits here?

Question

Greeting,

I have these three tables named - person, employee, customer. What SQL relationship should I use with the following criterion: 1. Person holds the preliminary data of the other two tables. 2. No employee-person can be customer-person.

To add more, I'm designing this with MySQL.

Should I use 1-1 or 1-* to the relationship of person->employee and person->customer? Your reply is higly appreciated.

Thanks!

Looks like you'll need the equivalent of a 1-* since you can't use a 1-1 because the link from person to the other table will be either the customer table or the employee table. — John K., Mar 02 '11 at 16:24
Employees can't be customers? Well that's one way to get around having an employee discount! — corsiKa, Mar 02 '11 at 16:24
@John: If I'm going to use 1-*, duplicates on PersonFK will exist for distinct CustomerPK or EmployeePK. — Cyril Horad, Mar 02 '11 at 16:34
Basically, you'd have a constrained foreign key from Customer into person and one from employee into person... so, The customer table would have to have an existing person record and the employee table would have to have an existing person record, but... the person record would not have to have an existing customer or employee record... You could not set up a foreign key from person TO the other 2 tables, because a foreign key needs to be from 1 table to 1 table... — John K., Mar 02 '11 at 16:39

Michael Goldshteyn · Answer 1 · 2011-03-02T16:34:28.573

1

You basically have a 1-(0 or 1) relationship between Person and the other two tables. The fact that Customer and Employee are mutually exclusive can be enforced via triggers. You can look at Section 3.6.3 of this explanation of relationship types for more info. Here is the Wikipedia article on exclusive relationships.

edited Mar 02 '11 at 16:34

answered Mar 02 '11 at 16:22

Michael Goldshteyn

71,784
24
131
181

What would be the mySQL constraint you allude to? – onedaywhen Mar 02 '11 at 16:27
I modified my answer to change constraint to triggers, which would be more correct for this type of (integrity constraint). – Michael Goldshteyn Mar 02 '11 at 16:34
@ondedaywhen: I can't say its a constraint to the tables. But a condition agreeing with the criterion said. – Cyril Horad Mar 02 '11 at 16:37

score 0 · Accepted Answer · answered Mar 02 '11 at 16:33

0

Here is an example for "tableinheritance" for sql-server, but should worl for mysql also: http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

answered Mar 02 '11 at 16:33

user570443

28
2

In table inheritance, does it solve the second criterion I sited? – Cyril Horad Mar 02 '11 at 16:51

score 0 · Answer 3 · edited May 23 '17 at 12:19

0

Your case looks like an example of the gen-spec design pattern. For previous discussions on this, follow the link. This should be simple. But how do I design this?

edited May 23 '17 at 12:19

Community

1
1

answered Mar 02 '11 at 17:20

Walter Mitty

18,205
2
28
58

What SQL relationship suits here?

3 Answers3