Creating a database table

Question

I am creating a database table for investigations, and I need to register the person who reported the incident, it can be an entry from the table of the supplier or user. The easiest way to do this is to have both the suppleir column and the user ID in my research table, but it seems wrong, what's the best way to do this?

Thanks.

Answer 1

You can have two more tables - IncidentsReportedBySupplier (IncidentID, SupplierID) and IncidentsReportedByUser (IncidentID, UserID) - which will IncidentsReportedByUser (IncidentID, UserID) empty columns.

But this also has disadvantages. Then you may have incidents that no one has reported.

Answer 2

I do not know why your option "seems wrong." I usually prefer your approach, having a column with FK for multiple tables. Your approach, although it may require zeros in another column, is much more straightforward and obvious. NO DOCUMENTS REQUIRED. There are no extra and extra tables ... just an extra column.

General rule of thumb, fewer tables = fewer relationships you need to worry about = less headache.

Answer 3

If both vendor and user tables have mutually exclusive unique identification numbers, you can have one column that uses this identifier as a "reporter".

If both of them have unique identification numbers, which can have overlapping values, you can use two columns for the identifier, for example:

 `reporter_id`, `reporter_type` 

where the type can be a value of type s or u to reflect table names. It will also eliminate all of these values ​​created by the method suggested by you.

Finally, if both tables do not have unique identification numbers, give them one! People data tables work much better with primary keys!