MS Sql normalization
-
We have a production table with a column called sheets. When we are making the final product in our production, sheets will be stored in another table called control_measures. This means we will only store sheets in production when we are making the final product. I want to know what you guys think. Should I normalize and move sheets to a new table related to production or should i make it nullable as it is? I know the recommended way of doing it in relational databases is normalization. But still, I´m curious to know what you guys have to say about this.
-
We have a production table with a column called sheets. When we are making the final product in our production, sheets will be stored in another table called control_measures. This means we will only store sheets in production when we are making the final product. I want to know what you guys think. Should I normalize and move sheets to a new table related to production or should i make it nullable as it is? I know the recommended way of doing it in relational databases is normalization. But still, I´m curious to know what you guys have to say about this.
If you were creating the tables at the moment I would certainly normalize it, that makes it easier to make changes in the future. If this is the current design and it works fine, I wouldn't touch it.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
If you were creating the tables at the moment I would certainly normalize it, that makes it easier to make changes in the future. If this is the current design and it works fine, I wouldn't touch it.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
So you prefer to normalize even if it is just one column? Whats wrong with a nullable column? And yes, this is the current design, i´m expanding.
-
So you prefer to normalize even if it is just one column? Whats wrong with a nullable column? And yes, this is the current design, i´m expanding.
Söderlund wrote:
So you prefer to normalize even if it is just one column?
Whats wrong with a nullable column?A nullable column is not dependent on the key it's linked to, while every atomic fact in the record should depend on the key. One can split the field of to it's own table with it's own identifying key. That's theoretically beautiful. If you were to implement the "beautiful" method, you'd end up with an extra table and an extra join.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
So you prefer to normalize even if it is just one column? Whats wrong with a nullable column? And yes, this is the current design, i´m expanding.
The proper answer you got from Eddy. Mine is that it depends, I'd rather normalize once to many than once to few. As I don't know anything about your domain, I also don't know if there are changes to your database to expect. But it's also about performance, most of the time (not always) normalization boosts performance in contrary to popular belief. The most obvious exception is OLAP. Here's an excellent article[^] on that subject. Whether or not nulls is a performance hit or not also depends on what database you're using, Oracle for example isn't ISO compliant in this matter and doesn't store NULL values at all, the lack of a value is the NULL value. SQL Server on the other hand stores a NULL token that takes two bytes for variable length data and the full space for fixed length data. So if you have a column with a high percentage of nulls you get a hit on memory compared to a separate table. If there's a low percentage of nulls you can keep it in the original table. And then again, SQL Server nowadays have SPARSE Columns. Can't say for sure how well it works as I have never used them. But at least in theory they should have fixed the problem, but give you an extra join for the null bitmap.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
The proper answer you got from Eddy. Mine is that it depends, I'd rather normalize once to many than once to few. As I don't know anything about your domain, I also don't know if there are changes to your database to expect. But it's also about performance, most of the time (not always) normalization boosts performance in contrary to popular belief. The most obvious exception is OLAP. Here's an excellent article[^] on that subject. Whether or not nulls is a performance hit or not also depends on what database you're using, Oracle for example isn't ISO compliant in this matter and doesn't store NULL values at all, the lack of a value is the NULL value. SQL Server on the other hand stores a NULL token that takes two bytes for variable length data and the full space for fixed length data. So if you have a column with a high percentage of nulls you get a hit on memory compared to a separate table. If there's a low percentage of nulls you can keep it in the original table. And then again, SQL Server nowadays have SPARSE Columns. Can't say for sure how well it works as I have never used them. But at least in theory they should have fixed the problem, but give you an extra join for the null bitmap.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
I´m kind of doing science here. No one has done this kind of system before and the company i´m working for have been turned down by programming firms doing production systems. To prepare database against future changes i would have to normalize every column since no one knows what or how things works until we have tested it. neither memory nor performance is an issue since we are talking about 6-10k records a year.
-
I´m kind of doing science here. No one has done this kind of system before and the company i´m working for have been turned down by programming firms doing production systems. To prepare database against future changes i would have to normalize every column since no one knows what or how things works until we have tested it. neither memory nor performance is an issue since we are talking about 6-10k records a year.
Söderlund wrote:
To prepare database against future changes i would have to normalize every column
Within reason, If you know your domain you can make a qualified guess as to were there will be changes. Remember that there is a disadvantage with normalizing all the way. Your CRUD operations will become complicated. There's a saying: Normalize 'til it hurts, denormalize 'til it works.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Söderlund wrote:
To prepare database against future changes i would have to normalize every column
Within reason, If you know your domain you can make a qualified guess as to were there will be changes. Remember that there is a disadvantage with normalizing all the way. Your CRUD operations will become complicated. There's a saying: Normalize 'til it hurts, denormalize 'til it works.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Indeed, I can guess and i have. However, I could not foresee this change. I´m split because on one hand i have "normalization is the way to go" and on the other hand i have "code that works". At the moment i will keep a nullable column and when it works i will look at normalization and how that will affect current code. I also believe it will be easier for future coders if i follow the standard guidelines.
-
Indeed, I can guess and i have. However, I could not foresee this change. I´m split because on one hand i have "normalization is the way to go" and on the other hand i have "code that works". At the moment i will keep a nullable column and when it works i will look at normalization and how that will affect current code. I also believe it will be easier for future coders if i follow the standard guidelines.
"Code that works" always trumps change "because it's the correct way of doing it".
Söderlund wrote:
if i follow the standard guidelines
Whos guidelines are those? Make your own guidelines instead, they're easier to follow
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Söderlund wrote:
To prepare database against future changes i would have to normalize every column
Within reason, If you know your domain you can make a qualified guess as to were there will be changes. Remember that there is a disadvantage with normalizing all the way. Your CRUD operations will become complicated. There's a saying: Normalize 'til it hurts, denormalize 'til it works.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Jörgen Andersson wrote:
Normalize 'til it hurts, denormalize 'til it works
It already hurts to read that. Normalize to 3NF, or better yet, BCNF. Denormalization should only be done when one can explain the trade-offs made, and the advantage gained.
Jörgen Andersson wrote:
Your CRUD operations will become complicated.
Only if you take a religious stance on optional fields. The other "recommendations" wouldn't impact the typical data-operations, nor complicate your queries.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Jörgen Andersson wrote:
Normalize 'til it hurts, denormalize 'til it works
It already hurts to read that. Normalize to 3NF, or better yet, BCNF. Denormalization should only be done when one can explain the trade-offs made, and the advantage gained.
Jörgen Andersson wrote:
Your CRUD operations will become complicated.
Only if you take a religious stance on optional fields. The other "recommendations" wouldn't impact the typical data-operations, nor complicate your queries.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
:laugh: Its not something I'm following, I prefer to get it as right as possible in the first go. I completely agree with you. It's just something I added to tease someone. With complicated I'm referring to that you get more to do the more tables you have, and the more tables with relations you have the more you have to do things in the right order. Each little operation is simple.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
We have a production table with a column called sheets. When we are making the final product in our production, sheets will be stored in another table called control_measures. This means we will only store sheets in production when we are making the final product. I want to know what you guys think. Should I normalize and move sheets to a new table related to production or should i make it nullable as it is? I know the recommended way of doing it in relational databases is normalization. But still, I´m curious to know what you guys have to say about this.
Why do you move the "Sheets" to a new table, it's still the same entity, isn't it? I would rather create a Sheet table with a status column, and let the production table and the control_measures table refer to it instead.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Why do you move the "Sheets" to a new table, it's still the same entity, isn't it? I would rather create a Sheet table with a status column, and let the production table and the control_measures table refer to it instead.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Quote:
I would rather create a Sheet table with a status column, and let the production table and the control_measures table refer to it instead.
I wasnt clear in my explanation but that was what i meant on how to solve the normalization.
Quote:
Why do you move the "Sheets" to a new table, it's still the same entity, isn't it?
I will try to explain. The production table stores shifttime and orders. So a new record will be added on new shifts or new order. When they are producing the final product, they also measure,controls and package sheets in stacks. So instead of having sheets stored over order/shift I want to store it on each stack. (One record for each stack in control_measures which is related to their shifts production table). Storing sheets in control_measures gives higher "resolution" and better data to serve to our customers. But only possible when producing the final product.
-
"Code that works" always trumps change "because it's the correct way of doing it".
Söderlund wrote:
if i follow the standard guidelines
Whos guidelines are those? Make your own guidelines instead, they're easier to follow
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Jörgen Andersson wrote:
Whos guidelines are those?
That´s what a friend was fed from school (I´m not schooled). So I Assumed it was standard, mostly because it makes sense. Not that i trust the school since they had a web developer program with a C# winform ball game exam and didnt touch php at all.
-
:laugh: Its not something I'm following, I prefer to get it as right as possible in the first go. I completely agree with you. It's just something I added to tease someone. With complicated I'm referring to that you get more to do the more tables you have, and the more tables with relations you have the more you have to do things in the right order. Each little operation is simple.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Jörgen Andersson wrote:
Whos guidelines are those?
That´s what a friend was fed from school (I´m not schooled). So I Assumed it was standard, mostly because it makes sense. Not that i trust the school since they had a web developer program with a C# winform ball game exam and didnt touch php at all.
I would trust the school a lot less if they taught PHP. Schools shouldn't teach languages, they should teach programming. C# in contrary to PHP enforces a lot of good habits. Not that you can't program properly in PHP, you certainly can. But this is a subject that others are much better at answering then I am. The best place to ask about this subject is probably the Lounge, but make damn sure it's not formed as a programming question but rather a discussion subject, or you might be well fried.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Quote:
I would rather create a Sheet table with a status column, and let the production table and the control_measures table refer to it instead.
I wasnt clear in my explanation but that was what i meant on how to solve the normalization.
Quote:
Why do you move the "Sheets" to a new table, it's still the same entity, isn't it?
I will try to explain. The production table stores shifttime and orders. So a new record will be added on new shifts or new order. When they are producing the final product, they also measure,controls and package sheets in stacks. So instead of having sheets stored over order/shift I want to store it on each stack. (One record for each stack in control_measures which is related to their shifts production table). Storing sheets in control_measures gives higher "resolution" and better data to serve to our customers. But only possible when producing the final product.
I believe I got stuck on the first paragraph in your OP. This sounds better, but I have too little info or domain knowledge to make a proper comment.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
I would trust the school a lot less if they taught PHP. Schools shouldn't teach languages, they should teach programming. C# in contrary to PHP enforces a lot of good habits. Not that you can't program properly in PHP, you certainly can. But this is a subject that others are much better at answering then I am. The best place to ask about this subject is probably the Lounge, but make damn sure it's not formed as a programming question but rather a discussion subject, or you might be well fried.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
I´m not gonna start a programming language war. My point was they shouldnt call it a web development course if they will spend 80% of the time making offline C# and java applications. It should be called "dip your toes into the programming water" course.
-
I believe I got stuck on the first paragraph in your OP. This sounds better, but I have too little info or domain knowledge to make a proper comment.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Yeah i guess so. Thanks for your time and input.