Of course the results differ, and to achive the correct result,
one have to write
AND NVL( F.OtherID, 1 ) = 1
My personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says
Don't know where you read this, but it cannot be right ...
Even in my original simple example you have two addresses in tho ORDERS table, so there is no chance in having 'the same field always the same name'.
Maybe you meant that a field with the same MEANING should have always the same name, like CUSTOMER, FORWARDER etc. ?
That would of course depend on which result is the correct one. I would also go very far to avoid a function in the where clause, for performance reasons.
The ISO standard I'm referring to is ISO-11179[^].
Considering the amount of text, I can't be bothered to find the actual paragraph that I'm referring to.
But the gist is that if you have a Customer Table the Surrogate Key should be named CustomerID in all tables using it no matter if it's the primary key or a foreign key.
It shouldn't be CustomerID in one table, CID in another, customer in a third, or just ID in the Customer Table. Same name everywhere to avoid confusion.
There are as always exceptions. An obvious one is when you have a Person table where the person have a work address and a home address, both columns referring to the same address table, they can't both be AddressID.
I don't need a creation date because the creation date is my first record with table_name and fk in this table. I could add a columns to add insert, update, delete information. With thi table I keep all my update dates and not only the last one.
Is this audit table a god practice ?
Can I also use this audit table to avoid conflict. You know when two users update the same record at the same time. It's possible to use the last update time to warn them there is a conflict.
I don't like the idea to have metadata like creation_date and update_date in a business oriented table.
It's just a good idea to do so. Makes sorting by creation-order easier, makes it easy to see when the table last was updated - but it's not an audit. It doesn't show who changed what, it merely shows when the object was created, and when it was last modified. You'll find those two attributes on a lot of things.
Fetching the top(update_date) gives a fast indication whether the table has changed since you last loaded it into memory.
Is this audit table a good practice ?
Depends; who's responsible for entering data into the new table? Does it happen automatically (using a trigger), or do you depend on the cooperation of the DAL-programmer?
I'd simply be dumping a copy of the entire record (in a separate table, different access-rights), including the user-name and domain. It'd be wasting less space if you only keep a track of the fields that are actually modified then when one makes a complete copy. OTOH, space is cheap nowadays
Can I also use this audit table to avoid conflict. You know when two users
update the same record at the same time. It's possible to use the last update
time to warn them there is a conflict.
..and then what? Ask the user to undo the work he's done and type it again? Merge the changes? What if they modified the same field?
It's a bit sweeter to "check out" (or lock) the record once a user starts editing. What sourcesafe does with files works equally well with records.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
Personally I don't consider that an audit solution, I say solution because audit is a whole lot of objects. We do a field level audit so we can track any changes to the data. Simply the ID for an inserted record, each field that is changed - only getting the from value and each field from a deleted record.
In theory this allows us to reproduce any action done by the users. Also we do not audit every table as the apps we build are based on other systems so we only track the master table changes.
The audit is initiated by a trigger spit, the ONLY valid use for the dammed things. This is our audit table.
Every table has a modified and modifiedby field. We also have a table of table names that allows us to run a script and apply/remove triggers from the target tables. As I said theres a whole ecosystem for auditing and it all lives in our model database so it is automatically included in every new database created.
In a vast number of years building LOB apps and some of them transactional I have never been hit by the condition of 2 users editing the same record except in 2 cases, contrived conditions to test the effect and in the early 90s using Microsoft Access in a multi user environment. It is a completed furfy IMNSHO.
Never underestimate the power of human stupidity
Depending on the amount of operations, once in a million could happen next monday.
No that isn't what that means at all.
If you have a business case where modification of the same record by two users can occur then the number of operations has nothing to do with it. When one has such a business case then one should of course take the necessary precautions, based on business needs (not just implementation hacking) to insure that a the best and most correct result is arrived at.
If there is no business case then the only way that can happen is due to poor design and/or implementation. Or perhaps just flat out wrong design/implementation.
Two of us get called by a customer and update the same issue in the bugtracker
First exactly what sort of business scenario is going to lead to two different customer employees to call at the same time to have one modify the very same bug?
Second presumably you are referring to that those two different customer employees are acting independently and both wish to resolve the bug, and not just comment on it, in different ways.
So exactly how, in terms of business usage, if those two same people called at different times would you correctly resolve updating the status two completely different ways? Say one calls on tuesday and says they want to close the bug as no longer applicable and a completely different person calls on wednesday and says they want to increase the priority to critical. Exactly how is your software, and only your software, going to resolve that? And just to make it more fun lets say the guy on tuesday is the CTO of the customers company and the guy on wednesday is a junior developer. (Keeping in mind of course that this is your scenario where two completely different people are interacting with your company at the same time.)
Third, "Whose result is the most 'best and correct'?", is exactly the question. That is business decision not a software decision. You cannot write software that is going to answer that question.
You have a customer with two different employees. Both of them call the service desk at 8:50am on Tuesday June 6th. Both want to do wildly different things to the same thing.
This means the customer has tasked them both with the same task. And that both reached wildly different outcomes with regard to what should happen.
No that doesn't seem like a scenario which is likely to occur and in fact is wildly unlikely to occur. Thus prioritizing this even to the extent of writing the requirement much less implementing it seems very unlikely.
Eddy Vluggen wrote:
But then again, we already noted that adding a record, as opposed to editing one, would solve that.
But again that is not the point. There are valid, probable and needful reasons for adding a record versus update that have nothing to do with the highly likely scenario that you have posited.
Always, this is the mantra I repeat at the start of every requirements meeting, I want it tattoo'd on the foreheads of every BA, I want "it is your job to interpret their requirements CORRECTLY" tattoo'd on their buts!
I'm old I've been doing this for a bloody long time, the number of times I have had this argument discussion with BAs and user are innumerable. I just hate it when I have to pound it into the head of a senior dev.
Mind you I'm delighted when they (the BAs and senior devs) come up with some novel concepts especially when they work.
Never underestimate the power of human stupidity
The installer every time was telling me that the installation failed and I did not know the reason however finally I got the solution the .exe package I was trying to install seems to be not complete as I download another one from microsoft site and when I tried again it worked fine.
I have a named instance of 2008 r2 located on my windows 7 professional local machine. I have tried to get my connect string to work but I just cant. I am using machine name "HOLDORF-PC" SQL instance name "SQL_2008_R2" and the rest of the connection string. Here is my connection string: