Member Avatar for 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?

  • 4 Contributors
  • 4 Replies
  • 368 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by Tinnin

Dani AI

Generated

Brief summary: ’s current design (one row that holds either a contact_id or a user_id, the other left NULL) is a common quick solution and will work functionally. It carries trade‑offs around integrity, querying, and future changes that are worth addressing before it becomes production tech debt.

If keeping the two nullable FK columns, make the integrity explicit. Add a constraint to guarantee exactly one is set (for DBs that support CHECK): 

CHECK ((contact_id IS NULL) <> (user_id IS NULL))

If the server ignores CHECK, enforce the rule with triggers or application-level validation. Also add indexes or unique constraints to prevent duplicate recipients per report (for example a UNIQUE on the logical recipient key). The downside: impossible to declare a single foreign‑key constraint that conditionally points at one of two tables, so referential integrity across users/contacts must be enforced outside a plain FK.

A cleaner, more robust approach is a single canonical recipient table and have report_recipient point to it. That allows a proper FK and makes deduping and auditing easier. Example pattern:

CREATE TABLE recipient (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  source_table ENUM('users','contacts') NULL,
  source_id INT UNSIGNED NULL,
  UNIQUE(email)
);

CREATE TABLE report_recipient (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_id INT UNSIGNED NOT NULL,
  recipient_id INT UNSIGNED NOT NULL,
  email_snapshot VARCHAR(255) NOT NULL,
  FOREIGN KEY (recipient_id) REFERENCES recipient(id),
  UNIQUE (report_id, recipient_id)
);

The email_snapshot preserves what was sent if the user/contact changes or is deleted.

For reads, ’s view or ’s UNION is handy to present a unified address list; those are good for selection and de‑duping but don’t replace the integrity benefits of a canonical recipient table. ’s PK note is a reminder to explicitly define keys and constraints. In short: for transient ad‑hoc sends the nullable pair is acceptable with strict validation; for long‑term logging, dedupe, and DB‑enforced integrity prefer the canonical recipient table plus an email snapshot.

Recommended Answers

All 4 Replies

Member Avatar for 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.

Member Avatar for 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 | contact2@site.tld | contacts  |
|  3 | random3@site.tld  | contacts  |
|  1 | user1@site.tld    | users     |
|  2 | user2@site.tld    | users     |
|  3 | user3@site.tld    | 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 | contact2@site.tld | contacts  |
|  3 | random3@site.tld  | contacts  |
|  1 | user1@site.tld    | contacts  | <- note the difference
|  2 | user2@site.tld    | users     |
|  3 | user3@site.tld    | 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.

Edited by cereal
Member Avatar for chrism_sci

primary key (id)

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.