This is probably better suited elsewhere, but the general forum is... well, general, so it's probably not too out of place given other discussions here.

Now, I have three tables, one with phone numbers (table_phone), one with rooms (table_rooms), and one with people (table_people). Each phone number is, of course, unique, and can be assigned to EITHER a room , a person, or neither, but never both.

table_people and table_rooms are quite different and serve different purposes elsewhere, so merging them isn't really an option. I COULD solve this through the application itself (PHP scripting), but would prefer an SQL solution. The server is currently running MySQL 5.0, but I might as well upgrade (or crossgrade to MariaDB) if a solution requires commands only available in newer versions.

If I make unique foreign keys id_people and id_rooms in the table_phone, they are different columns, so the phone number may very well be assigned to a person and a room at the same time.

If I make a unique foreign key id_phone in table_rooms and table_people, they have no inherent uniqueness (only in the same table) and the situation is the same as in the previous case.

I haven't actually begun building the application, beyond planning the database, so changes are quite easy to implement.

The question: How can I make sure a phone number is used at most once, across two different tables?

I'm not completely familiar with MySQL, but you might want to look at using a trigger.

I would say you have table_phone contain id_people and id_rooms as unique foreign keys as you said, but use a trigger to monitor updates to make sure they both don't get set.

At least that's my thoughts. I've not had reason to use triggers before, but that seems like one.

From what I remember there is an exclusive OR in SQL so it should be possible to filter the input using it...

The problem (foreign key may reference an object of one class or another, not both at once) is not one that yields to ordinary normalization. SQL just doesn't do that in a simple way.

For me, it's easier to deal with if you don't muck up the phones table with extra columns. Put the "phone" column on the people table and the rooms table.

A question not yet answered that affects how you could implement this is, Does a "person" or "room" always have a phone number? (From another angle, should the "phone" column of these tables be NOT NULL)?

The reason I'm going that way is, I want to be able to enforce a condition that a phone (literally, a foreign key to the phones table) is unique across all people and rooms.

I'm still trying to work out how this can be made to work best in MySQL. Every database product has different limitations on what you can define an index over. But where I'm trying to go is something like this:

CREATE VIEW phone_person_or_room AS
(SELECT phone FROM table_rooms)
UNION ALL
(SELECT phone FROM table_people);

CREATE UNIQUE INDEX i_phone_belongs_person_or_room ON
phone_person_or_room (phone);

That's a nice solution. I didn't realize that you could build a unique index on a view in order to enforce uniqueness between two fields. Very nice.

Non-portable. Many databases won't allow it; they don't allow views to be objects of indexes. But it's in many ways ideal, when you can make it work.

I have a feeling that this sort of behavior is not allowed on the DBMS I use. However, it's always good to learn something that could be useful later. +1

The downside to that is that (at least under Oracle DB) you can end up with problems in the refresh process because modifications are not made with the same order as the modifications to the master table (you can sort of fix that by using integrity constraints on the master table, because the view is just a copy of the table itself [sort-of]).

If you can accept not actually having a foreign key, you can add two fields to table_phone: an ENUM(person, room), which specifies whether it's asosciated to a person or a room, and a second field which then contains the ID of the actual person or room (likely INT, but I don't what data types you're using).

Make at least one of these nullable if it's permitted to have an unassigned phone number, and add a unique index containing both of these fields if only one number can be assigned to a given person/room. MySQL (and AFAIK, the SQL standard as well) permits multiple rows with a NULL value, even if there's a unique index on the column.

You can then add triggers to disassociate phone numbers when a row is deleted from table_person or table_room (assuming, of course, that this is ever necessary).

Hmm, you have three tables:

table_number
table_room
table_people

You have the number as a unique key in table_number. What other fields are in table_number? I'd recommend a second field that gives a 'type' so that each phone number is listed as either a people number or a room number.

Then when you use your front-end forms to add numbers, you assign them a type. Then using simple select queries on this table (as views) you can then select appropriate numbers for entry into the other two tables. You could also look at enforcing the integrity of the tables but I'm not sure how you'd do this without actually working with the system itself. But if you can use the above to control how data is entered into the database then it should look after itself.

Neither person nor room requires a number, it can be null in both cases. Hmm interesting, I'll have to try that, sounds like it might work.

Nothing yet, just an auto-incrementing ID (INT) and the number (VARCHAR, to keep initial 0's).

Wouldn't a unique index for two columns just make sure that both are not the same twice? Ie. if the columns are (4,5) in one row, they are allowed to be (4,6), (4,null), (5,5), and (null,5) in others?

If going this route, I would be looking for something that forces one of the columns to null when the other is not null (but both can be null, in case the phone number has no assignment).
Person being removed is one reason for a number to lose its assignment, yes.

After thinking about it further, it seems strange to be using a logical view to enforce rules on a physical table.

I still offer triggers as an idea. Have one on the room table and one on the people table and it checks to make sure the number is not already in use in the other table.

Correct, but the idea is that you use the *same* column for the ID, regardless of whether it belongs to a person or a room, and then just write in a *different field* if the ID refers to a person or a room. That means you get values like (person, 2) and (room, 4).

It makes for a slightly more complex query when joining, but it ensures that no number can be assigned twice, and by placing a unique index on those two fields, you also ensure that no one can have two numbers assigned.

If you need me to make a more concrete example, just let me know.