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?