Converting mySQL tables to a 2NF relation

I'm currently going through the process of learning about database normalization & having a crack at creating my first relational database. It was going well until I hit the following problem.

In a non-normalised form my justifications table would like like this:

| justification_id | user_id | question | response | action | category | category_id | submitted_at |
-----------------------------------------------------------------------------------------------------

Open in new window


My research & thought process led me to the following:

justification_category

| justification_id | category | category_id | 

Open in new window


justification_response

| justification_id | question | response | 

Open in new window


justification_action

| justification_id | user_id | action | submitted_at |

Open in new window


How does a justification work?

When a user performs an action on a category ( the current categories being tickets, orders, accounts ) a justification is required by the user to state why they are performing that action. I.e. account deletion

An action can have multiple questions attached resulting in multiple responses. For example: Deleting an account would ask the questions: Have you followed our internal steps? Why do you need to perform this action? etc. This is why I proposed the justification_response table because it will not store any NULL fields.

The justification_action table will log the action performed, the user_id ( this is the submitter of the action ) & the time of the action.

Questions

I currently have 3 potential categories where a justification is required on an action, they're tickets, orders & accounts. With this in mind would it be a better approach to have 3 separate tables to describe each category? Would this approach meet 2NF?

I feel I am far away from a 2NF relation. This is because I'm constantly re-using the justification_id as either a FK or a PK. How far away am I?
JakehallasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
I think you're close, but you're connecting some things that aren't logically connected as far as I can tell.

So you have 3 categories ...full stop.  That's a table.  You probably need category_id and category_name (or similar), along with any other category-level meta data.  

You mentioned "When a user performs an action on a category", but it does not sound like the user is taking an action on the category.  Rather, they would take an action on an item within a category.  For example, you're not deleting accounts - you're deleting *one* account.  Or multiple, perhaps, but that is still an action on an item.  Given that your examples (accounts, orders, tickets) are not very similar items, I would recommend separating their storage.  

Now, regarding the actions taken, this would be your "justifications_action" table.  You need a PK id field (autoincrement is fine), as well as the user_id, the category_id, and the id of the item within the category.  The action, and a timestamp, of course, can also be stored here.  The category_id points the way to information about the type of item, and category_id plus item_id can identify the exact item.  

Moving a little forward, I am assuming the questions you ask are based on 1) the category, and 2) the type of action being taken.  That implies the need for a table to catalog the available actions on any given category.  That table will need a PK, category_id, and associated meta data for the action (name, etc.).  

Then we get to the questions themselves.  Again, a PK (always a PK), and the fields necessary to trace its "lineage", so to speak.  If you're using the previous idea of a category_actions table, then category_action.PK=questions.FK.  If you're not using that intermediary table, then category.PK=questions.FK.  

Finally, we get to the response table, which should hold (three guesses...) a PK, an FK pointing to justifications_actions.PK, and an FK pointing to questions.PK.  I'm assuming your response field is free text, or there are further complications.

So, to recap:
tbl_category
   id (PK)
   title

tbl_category_actions
  id (PK)
  category_id (FK=tbl_category.id)
  action_name

tbl_justification_actions
  id (PK)
  category_id (FK=tbl_category.id)
  item_id (FK=tbl_actual_item.id)
  action_id (FK=tbl_category_actions.id)
  ts (timestamp)

tbl_action_questions
  id (PK)
  action_id (FK=tbl_category_actions.id)
  question_text

tbl_responses
  id (PK)
  justification_id (FK=tbl_justification_actions.id)
  question_id (FK=tbl_action_questions.id)
  response_text

Open in new window

This is just off the cuff - it could probably be streamlined a bit more.  For example, tbl_justification_actions.category_id is redundant, since that lineage can be traced through action_id.  It depends on your actual usage - you'll have to see if it is worthwhile to you to repeat that data for more streamlined data manipulation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arnoldCommented:
Presumably category is identified by category_id
Providing a single table heading without the info/content one has to determine what it supposed to mean. Question us not unique for a user,
Is the table you are dealing with is the transactional table where a user sees a question, chooses the answer and you need to record that.
Question is within the category scope as well as the response?......

Your criteria UserA to complete action 1 has to address/respond to a set of questions.
Justification presumably are the responses of the user to the questions to complete the task/action.
JakehallasAuthor Commented:
I cannot thank you enough for that answer; it has saved me a lot of headaches!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.