CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Cyril Horad
Cyril Horad

Reputation: 1565

What SQL relationship suits here?

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!

Upvotes: 2

Views: 242

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

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?

Upvotes: 0

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74360

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.

Upvotes: 1

user570443
user570443

Reputation: 28

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

Upvotes: 0

Related Questions