2
Example: I have an Orders table, the order has some status, new, delivered, canceled etc. When the status is canceled I will give the option of the user to tell the reason that was canceled, this reason must be an attribute of the orders table or must be a table to part?
2
It depends on how you want to manipulate this data later. If that reason is just to be recorded, then make a column there on your table and store it. Now if you later need to manipulate this data, even order status, it might be interesting to create a new table. It depends a lot on what you want, and also on the complexity.
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 go in pieces:
If the user can only choose from a list of predetermined options (i.e., he(a) cannot enter an arbitrary reason), then to keep the table normalized (eliminating redundancies, making it easier to update these values in the future) you would create a new table with possible reasons for cancellation, and have a foreign key in the order table "pointing" to that new table.
If the user can enter with arbitrary motives - for example, the field to be filled is a text field, or there is a selection of motives 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.
Standardisation is not a panacea that works for all situations. For example, if you’re going to have a lot more writing than reading operations that need to know the reason, denormalizing the table (i.e., having the reason as a field in the order table) can make more sense, even if the potential motifs are a small set.
Another case where normalization may not be recommended is if there is a possibility that you may need to distribute your tables. If tables need to be partitioned (a problem you probably only have if you have been working with a high-scale database, or if it needs for some other reason to be geographically distributed), the storage gains and conciseness of normalization would not compensate for the gain of multiple partition joins.
There would be some fixed reasons and an option for another that can be inserted by the user
If you imagine that most users will choose one of the default options, then you can have a gain in creating an extra table (even if in this table you need to store the user’s "other" motives). As always, it depends :)
Browser other questions tagged database relational-model attributes
You are not signed in. Login or sign up in order to post.
The status is already a table the part because I need to manipulate, the reason is just to keep even..
– Mateus Carvalho