CODEWITHSUNDEEP
mysqldatabase-designdatabase-normalization
SaidbakR
SaidbakR

Reputation: 13534

Database normalization of an entity with varied properties

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?

Upvotes: 0

Views: 51

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

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.

Upvotes: 2

André Abboud
André Abboud

Reputation: 2030

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

Upvotes: 0

Related Questions