|
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.
|
|
|
|
|
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 
If you can't read my code, try converting it here[^]
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
"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.
|
|
|
|
|
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.
|
|
|
|
|
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 
If you can't read my code, try converting it here[^]
|
|
|
|
|
 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: It's just something I added to tease someone.
Jörgen Andersson wrote: Each little operation is simple.
Can't argue with that.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
how can I establish a link between two of my database server which have the platform sql
|
|
|
|
|
|
Very easy to do. Like this:
USE[master]
EXEC sp_addlinkedserver N'{computer}\{database},
N'SQL Server'
Literally ...
|
|
|
|
|
I have a database that receives weather information. The main table is (somewhat simplified)
LocationID int, Hour int, HiTemp Float, LoTemp Float, TimedTemp Float
Temperature data is first written to a holding table - LocationID int, Hour int, whichVariable int, value float. Then it is merged into the main table (using a MERGE statement)
All this works fine when the temperature data written to the holding table goes in one variable at a time, executes the MERGE, loads the next variable, etc.
If I have multiple programs adding multiple locations and different variables all at the same time, the system deadlocks. I could force one instance of the outside program inserting the data to use a mutex to guarantee undisturbed calls to MERGE but it is quite possible we will have multiple instances of the outside program running.
What is the best approach to take to allow merging of the data?
|
|
|
|
|
Why are you using merge instead of an insert?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The routine uses the MERGE statement to determine if a record for the particular observation exists. If it does not, it inserts a new record. If a record does exist, it updates the appropriate field.
Problem comes in that there are three raw import records for the particular observation. So the first merge may try to insert something for a high temperature while at the same time the system is trying to insert for the low temperature.
I can use a cursor to cycle through the imported data row by row but want to avoid the speed hit.
I also can't change the main table to hold a record for each individual type of temperature because of the huge number of records (~17 million per observation cycle)
|
|
|
|
|
Have only one thread perform the MERGE portion?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
