8

I am creating a database table for investigations and i need to log the person who reported the incident, this could be a record from the supplier or user tables. The easiest way to do this would be to have both a suppleir and a user id column in my investigations table but that seems wrong, what's a better way to do this?

Thank you.

slex
  • 855
  • 2
  • 10
  • 20
  • Are suppliers people or companies? If they are people, why can't they share a table with other users? (You need a contact person/information for a company, so you might want to put all the persons into single `users` table and make links to other tables accordingly, ie. from `users` to `company`. Thus you have truly unique ID for every person in you DB no matter who they are and who they work for and you need only one column for incident reporter.) – ZZ-bb Aug 20 '12 at 09:59

3 Answers3

1

You could have another two tables - IncidentsReportedBySupplier (IncidentID, SupplierID) and IncidentsReportedByUser (IncidentID, UserID) - which would remove the empty columns.

But this has disadvantages too. You can then potentially have incidents which aren't reported by anybody.

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • He states that he must log the person that reports the incident. As long as there is always one "reporter," he'd best avoid creating an intermediary table (which is best for many->many relationships), much less two such tables. – Bryan Wolfford Aug 20 '12 at 09:43
  • Creating an intermediary would be a better option than having two redundant tables to store practically the same data. You should not have to search several places to find the "incident" – sam yi Aug 21 '12 at 19:07
-1

I don't know why your option "seems wrong". I generally prefer your approach over having a column with FK to multiple tables. Your approach, while it may require nulls in the other column, is much more straightforward and obvious. NO DOCUMENTATION REQUIRED. No unnecessary and redundant tables... just an extra column.

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

sam yi
  • 4,806
  • 1
  • 29
  • 40
  • 1
    That's a dreadful rule of thumb. Why not throw all the data into one table? Or Excel. – podiluska Aug 21 '12 at 09:38
  • Normalization should be planned carefully based on the type of data you have and its usage. 2NF is not the answer for everyone and every scenario. Do you not agree 3 tables would be easier to maintain than 15? Why so dreadful? 15 seems more dreadful to me. – sam yi Aug 21 '12 at 19:08
  • Over-normalization often leads to performance problems... no need to have a table with data types.. ;) But with a smaller system... where performance is not an issue... follow the KISS rule. (Keep it simple stupid) – sam yi Aug 21 '12 at 19:10
  • No, I don't agree that a starting with a poor design, where every rule of database design is violated for some mistaken ideal of ease of maintenance, is a good idea. That's not simple, its convuluted and obscure. That sounds more like the Keep It Stupid for Simpletons rule. – podiluska Aug 21 '12 at 19:41
  • There is no "rule" of database design just guidelines. If there were "rules" why do we need architects? Just follow the "rules" the same way we do basic algebra. Every system is different with different needs. You don't mean to suggest every database should be normalized to a XNF?!?! My comment was not as "dreadful" as you think... ;) – sam yi Aug 21 '12 at 20:07
-2

If both supplier and user tables have mutually exclusive unique ID numbers, you can have one column that uses that ID as the "reporter."

If they both have unique ID numbers that may have overlapping values, you can use two columns for the ID like: 

`reporter_id`,
`reporter_type`

where the type could be an value like s or u to reflect the table names. This would also eliminate all those null values created by the method your proposed.

Finally, if they both tables do not have unique ID numbers, give them one! Tables of data about people work much better with primary keys!

Bryan Wolfford
  • 2,102
  • 3
  • 20
  • 26
  • I said it could be doesn't have to be... other options, since there are only two possible values are: TINYINT where 0 is supplier, 1 is user VARCHAR(1) where 's' is supplier and 'u' is for user The idea is to reduce future confusion by having the value self document itself. – Bryan Wolfford Aug 20 '12 at 09:46
  • 2
    This is a form of multivalued dependency, and as such a violation of 4th Normal Form. – podiluska Aug 20 '12 at 10:21