Hello, I have (hope correctly) normalized my table from 0NF to 2NF.
How do I go from 2NF to 3NF?
Anyone?
Also, ResponseToTransactionId is a recursive foreign key that will contain NULL values a lot of the time. Is this bad design?
Transactions structure:
As long as a given Transaction is for only one single Item SKU, this is correct as is.
I would move "Response to Transaction Id" to a separate table, but that is based on experience of adminstering and using such a table, NOT on pure 3NF considerations.
Items structure:
As long as an Item can be in only a single Category, this is correct as is.
Departments / Admins
Need adjusted as follows::
Add Structure:
Deparment_Admins ( Department Id, Admin Id, Date Assigned )
Adjust Structures:
Departments ( Department Id, Department Location ) --removed Department Admin[s]
Admins ( Admin Id, Admin Username, Admin Password ) --removed Admin Deparment[s]
Note that the existing structures had redundant data: the Admin password was repeated in multiple rows. Normalization tries to get rid of data redundancy as much as reasonably possible, because it can cause maintenance issues.
Furthermore, if there is data directly related to a relationship between structures, then the relationship should have its own structure. In this case, the relationship between Department Id and Admin Id would have its own data = the date that that Admin was assigned to that Department. Note that this data does not directly relate to either Admin or Dept by itself, but specifically to the intersection / assignment of the two together.
1 Like
Thank you @ScottPletcher
You gave me new things to think about when it comes to the Transaction and Item structure. To make it simple, I will use that an transaction contains only a single SKU and that an item can only be in one category.
I will also take your advice on moving the ResponseToTransactionId to a seperate table.
Is moving the many-to-many relationship between Departments and Admin into its own table part of 2NF? Or do I do it now when going from 2NF to 3NF?
No, the new relationship (intersection) table between Departments and Admin is part of 3NF modeling.
My post was in response to your q of:
How do I go from 2NF to 3NF?
1 Like
These are the changes from 2NF to 3NF:
Thank you for your help.
Edit
Btw, what do you think about moving Transaction Method to its own table and using foreign key IDs instead?
I would likely do that.
Personally, I would also use a separate Item Categories table to hold those descriptions, but that may be overkill for some. I still think in the back of my mind somewhere that an Item could eventually belong to more than one category (in a real-life situation) and that also leads me to design the table so that such changes are easier to make later if required.
1 Like
How about the case where a department has n+1 Admins.
Never mind....what I meant to say is can one Dept have 2 Admins at the same time? Or if this is historical are you missing fromdate todate columns
Yes, a department can have many admins at the same time.