0

In my application I have an entity, table, called actions with varied properties. To clarify the case, the following is the table actions structure:

  1. id,
  2. status_id(not null),
  3. section_id(not null),
  4. job_id (not null)
  5. equipment_id (null),
  6. cause_id (null),
  7. solution_id (null),
  8. created_at,
  9. closed_at,
  10. action_type (not null) char(3)

Where all fields suffixed with _id are foreign keys and the action_type is very limited and defined list of actions types, so I defined it in a configuration file i.e there is no database entity for action_type.

My question is more general than this one: Can a foreign key be NULL and/or duplicate? where I'm asking about normalization principal.

In my case, some action types has no need, for example, for equipment_id, where others need equipment_id but not need both cause_id and solution_id, etc

In my database design, the actions table looks like Many to Many conjugation table.

The above design allows, easily, to get many statistics data about sections and jobs without need to perform complex join queries.

My question is: Does my normalization and design correct?

Community
  • 1
  • 1
SaidbakR
  • 13,303
  • 20
  • 101
  • 195
  • What do you mean by "normalization"? That word is used to mean a lot of different things. Instead of that word, please explain what you are trying to achieve and why. And if you are refering to a particular use of it, please give a reference. Also, please rephrase the unclear "some action types has no need, for example, for equipment_id, where others need equipment_id but not need both cause_id and solution_id, etc". – philipxy May 12 '17 at 03:43
  • @philipxy well it works like actions log table. That log is going to record some data about actions including, its type, equipment used , defect, cause, solution if any of them applicable. – SaidbakR May 12 '17 at 04:24
  • You didn't answer my question. By "explain what you are trying to achieve and why" I meant, by using "normalization". – philipxy May 12 '17 at 04:48

2 Answers2

2

Yes. A foreign key containing a NULL represents a case where a relationship is optional, and the relationship is not present in this instance.

In your case, there may be entries where there is simply no corresponding equipment, and equipment_id is accordingly left NULL. When a join is done to the reference table, rows with NULL in the foreign key will simply drop out.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
0

Yes if these _id are not so important so in normalization you can set them as null or remove them :)

André Abboud
  • 1,864
  • 2
  • 14
  • 23