Example: I have a Orders table, the order has some status, new, delivered, canceled etc. When the status is canceled I will give the user the option to say the reason that was canceled, this reason must be an attribute of the requests table or should it be a separate table?
When should an attribute be a table or vice versa?
2
asked by anonymous 13.01.2016 / 02:12
2 answers
2
The usual answer: it depends. Your question is basically about normalization: if you want to keep your database normalized, it might be that using an extra table is interesting. But not always. Let's break it down:
What are the possible reason for cancellation?
If the user can only choose from a list of predetermined options (ie, he can not enter an arbitrary reason), then to keep the table normalized (eliminating redundancies, making it easier to update those values in the future) you would create a new table with the possible reasons for cancellation, and would have a foreign key in the order table "pointing" to this new table.
If the user can enter with arbitrary motifs - for example, the field to be filled in is a text field, or there is a selection of motifs with the "other" option where the user can enter any text - then you will not gain much in normalizing the table, since potentially all the reasons will be different.
How will you use this information?
Normalization is not a panacea that works for all situations. For example, if you are going to have a lot more writing operations than reading you need to know about the reason, denormalizing the table (ie, having the subject as a field in the order table) may make more sense even if the potential reasons are a small set.
Other cases where denormalization may make sense
Another case where normalization may not be recommended is if there is a possibility that you need to distribute your tables. If tables need to be partitioned (a problem you probably only have if you are working with a high-scale database, or if it needs to be otherwise geographically distributed), storage gains and conciseness of normalization would not compensate for the gain of multiple partition JOINs.
13.01.2016 / 02:20
2
It depends on how you want to manipulate this data later. If this reason is only to be registered, then make a column there in your table and store it. Now if you later need to manipulate this data, even the order statuses, it may be interesting to create a new table. It depends a lot on what you want, and also on complexity.
13.01.2016 / 02:17