One of a pair of columns always null

[Tinnin]

Hi All,

I'm not sure if this is a technical issue, preference or a question about best form.

I have a 'users' table and a 'contacts' table. 'users' and 'contacts' have no direct relation. Both contain an email address for each record in their own table. I want to send reports to both users and contacts. I have a 'reports' table with some details of each report to be sent. To send the reports I am matching up a report_id to a user_id or a report_id to a contact_id. So I have the following query to create this table:

create table if not exists report_recipient (
    id mediumint unsigned not null auto_increment,
    report_id mediumint unsigned not null,
    contact_id mediumint unsigned,
    user_id int unsigned,
    primary key (id)
)

For each record in 'report_recipient' either 'contact_id' or 'user_id' will have a value, never both (i.e. one will be NULL).

My question is this: Is this an acceptable way to do it or would it be preferable to have separate tables for user recipients and contact recipients?

Or is there a third way?

[JorgeM]

How about creating a view instead and only including those records in the view where there is a match on report_id in each table? The view would only need one field, email_address and would not contain null values.

[cereal]

Also, if you still want one query to return them all, you could use union in a subquery:

select id, email, tablename from (select u.id, u.email, 'users' as tablename from users as u union select c.id, c.email, 'contacts' as tablename from contacts as c) as sub group by email;

It will return something like this:

+----+-------------------+-----------+
| id | email             | tablename |
+----+-------------------+-----------+
|  2 | [e-mail] | contacts  |
|  3 | [e-mail]  | contacts  |
|  1 | [e-mail]    | users     |
|  2 | [e-mail]    | users     |
|  3 | [e-mail]    | users     |
+----+-------------------+-----------+
5 rows in set (0.00 sec)

Avoiding duplicates between the two tables, and in case one of the two is deleted, the following query will display the result from the other table, so you don't have to keep track of changes:

delete from users where id = 1;

select id, email, tablename from (select u.id, u.email, 'users' as tablename from users as u union select c.id, c.email, 'contacts' as tablename from contacts as c) as sub group by email;

+----+-------------------+-----------+
| id | email             | tablename |
+----+-------------------+-----------+
|  2 | [e-mail] | contacts  |
|  3 | [e-mail]  | contacts  |
|  1 | [e-mail]    | contacts  | <- note the difference
|  2 | [e-mail]    | users     |
|  3 | [e-mail]    | users     |
+----+-------------------+-----------+
5 rows in set (0.00 sec)

Live example: http://sqlfiddle.com/#!9/c8c13/2

But I'm not sure this is what you want. If it's for logging then I would use separated tables.

[chrism_sci]

primary key (id)

[Tinnin]

Thanks for the input guys. I'd considered the union option in terms of the select query but was thinking more in terms of how to store the data. I'll have a look at using a view. Not used one before though so I guess I'll hit the books.