Validate that value is unique over multiple tables access

Question

Scenario: I have to create a database which has to contain 3 different tables holding information about people. Lets call them Members, Non_Members and Employees. Among the other information they may share , one is the telephone number. The phone numbers are unique, each in its respective table.

My problem: I want to make sure the phone number is always unique among these 3 tables. Is there a way to create a validation rule for that ? If not and I need to redesign the database, which would be the recommended way to do it.

Additional info: While the 3 tables hold the same information (Name , address etc.) its not required always required to fill them. So I am not sure if a generic table named Persons would work for my case.

Some ideas: I was wondering if and how I can use a query as a validation rule (that would make things easier). If I would end up creating a table called Phone numbers , how would the relations between the 4 tables would work in order to ensure that each of the 3 tables has a phone number.

ERD

Answer 1

I assume you are talking about a relational database.

I would go for a single person table with a "type" column (member, non_member, ...). That is much more flexible in the long run. It's easy to add new "person types" - what if you later want a "guest" type?

You would need to define as nullable to cater for the "not all information is required" part.

With just a single table, it's easy to make the phone number unique.

If you do need to make it unique across different tables, you need to put the phone numbers in their own table (where the number is unique) and the references that phone_number table from the other tables.

Edit

Here is an example of creating such a phone_number table:

create table phone_number 
(
   id integer primary key, 
   phone varchar(100) not null unique
);

create table member 
(
   id integer primary key, 
   name varchar(100), 
   ... other columns
   phone_number_id integer references phone_number
);

The tables non_member and employee would have the same structure (which is a strong sign that they should be a single entity)

Edit 2 (2016-01-08 20:12)

As sqlvogel correctly pointed out, putting the phone numbers into a single table doesn't prevent a phone number to be used by more than one person (I misunderstood the requirement so that no phone number should be stored more than once)