March 7, 2018 at 4:21 pm
Are you able to help on this?
Greatly appreciate !
March 7, 2018 at 4:47 pm
ScottPletcher - Wednesday, March 7, 2018 2:52 PMJason A. Long - Wednesday, March 7, 2018 2:41 PMThe really easy thing would be simply join the modern era and just use email address like every other website built in the last 10 years.Nothing will make me stop doing business with a company faster than a website that won't let me login because I can't remember some nonsensical username.
Bit extreme, don't you think? I guess you use the same email address for everything; I certainly don't. I use bogus ones for a lot of (retail) companies so they don't have my real email address.
Also, I would run like he!! from any bank or credit card or brokerage company, as examples, that used email to log in. How many gazillions of people know your email?
No... Not really... A username isn't exactly a security feature. I rely on reasonably complex passwords for that...
If you're running from financial institutions that use your email as part of the login process, you must be doing quite a bit of running. Most the major financial institutions that I'm aware of (or at least the ones I use) all use multi-factor authentication that includes email address. As long as I'm using a devise that I've previously authenticated, my logins are as simple as providing my email address (as my account id) and my password.
March 7, 2018 at 4:58 pm
Could this be done in a simpler manner? Maybe, but here is one way.
WITH base AS (
SELECT
[t].[ID]
, [t].[FIRSTNAME]
, [t].[LASTNAME]
, [t].[USERNAME]
, [ca1].[UserName] AS [BaseUserName]
, ISNULL([ca2].[v1],-1) AS [UserNumeric]
FROM
#Temp t
CROSS APPLY(SELECT [t].[FIRSTNAME] + '.' + [t].[LASTNAME])ca1(UserName)
CROSS APPLY (SELECT CAST(STUFF([t].[USERNAME],1,LEN([ca1].[UserName]),'') AS INT))ca2(v1)
), UserMax AS (
SELECT
.[BaseUserName], MAX(.[UserNumeric]) AS MaxNumeric
FROM
[base] AS
GROUP BY
.[BaseUserName]
), UpdatingValues AS (
SELECT
[b1].[ID]
, [b1].[FIRSTNAME]
, [b1].[LASTNAME]
, [b1].[USERNAME]
, [b1].[BaseUserName]
, [b1].[UserNumeric]
, [ca3].[BaseUserName] + CAST([ca3].[MaxNumeric] + DENSE_RANK() OVER (PARTITION BY [b1].[BaseUserName] ORDER BY [b1].[ID]) AS VARCHAR(30)) AS NewUserName
FROM
[base] AS [b1]
CROSS APPLY (SELECT * FROM [UserMax] AS [um] WHERE [um].[BaseUserName] = [b1].[BaseUserName])ca3(BaseUserName,MaxNumeric)
WHERE
[b1].[UserName] = ''
)
UPDATE [t] SET
[USERNAME] = [uv].[NewUserName]
FROM
[#Temp] AS [t]
INNER JOIN [UpdatingValues] AS [uv]
ON ([t].[ID] = [uv].[ID]);
GO
March 7, 2018 at 5:49 pm
Lynn Pettis - Wednesday, March 7, 2018 4:58 PMCould this be done in a simpler manner? Maybe, but here is one way.
WITH base AS (
SELECT
[t].[ID]
, [t].[FIRSTNAME]
, [t].[LASTNAME]
, [t].[USERNAME]
, [ca1].[UserName] AS [BaseUserName]
, ISNULL([ca2].[v1],-1) AS [UserNumeric]
FROM
#Temp t
CROSS APPLY(SELECT [t].[FIRSTNAME] + '.' + [t].[LASTNAME])ca1(UserName)
CROSS APPLY (SELECT CAST(STUFF([t].[USERNAME],1,LEN([ca1].[UserName]),'') AS INT))ca2(v1)
), UserMax AS (
SELECT
.[BaseUserName], MAX(.[UserNumeric]) AS MaxNumeric
FROM
[base] AS
GROUP BY
.[BaseUserName]
), UpdatingValues AS (
SELECT
[b1].[ID]
, [b1].[FIRSTNAME]
, [b1].[LASTNAME]
, [b1].[USERNAME]
, [b1].[BaseUserName]
, [b1].[UserNumeric]
, [ca3].[BaseUserName] + CAST([ca3].[MaxNumeric] + DENSE_RANK() OVER (PARTITION BY [b1].[BaseUserName] ORDER BY [b1].[ID]) AS VARCHAR(30)) AS NewUserName
FROM
[base] AS [b1]
CROSS APPLY (SELECT * FROM [UserMax] AS [um] WHERE [um].[BaseUserName] = [b1].[BaseUserName])ca3(BaseUserName,MaxNumeric)
WHERE
[b1].[UserName] = ''
)
UPDATE [t] SET
[USERNAME] = [uv].[NewUserName]
FROM
[#Temp] AS [t]
INNER JOIN [UpdatingValues] AS [uv]
ON ([t].[ID] = [uv].[ID]);
GO
Amazing......
Thank you so much, You save my days.
Appreciate !!!
March 7, 2018 at 8:06 pm
yogi123 - Wednesday, March 7, 2018 5:49 PMLynn Pettis - Wednesday, March 7, 2018 4:58 PMCould this be done in a simpler manner? Maybe, but here is one way.
WITH base AS (
SELECT
[t].[ID]
, [t].[FIRSTNAME]
, [t].[LASTNAME]
, [t].[USERNAME]
, [ca1].[UserName] AS [BaseUserName]
, ISNULL([ca2].[v1],-1) AS [UserNumeric]
FROM
#Temp t
CROSS APPLY(SELECT [t].[FIRSTNAME] + '.' + [t].[LASTNAME])ca1(UserName)
CROSS APPLY (SELECT CAST(STUFF([t].[USERNAME],1,LEN([ca1].[UserName]),'') AS INT))ca2(v1)
), UserMax AS (
SELECT
.[BaseUserName], MAX(.[UserNumeric]) AS MaxNumeric
FROM
[base] AS
GROUP BY
.[BaseUserName]
), UpdatingValues AS (
SELECT
[b1].[ID]
, [b1].[FIRSTNAME]
, [b1].[LASTNAME]
, [b1].[USERNAME]
, [b1].[BaseUserName]
, [b1].[UserNumeric]
, [ca3].[BaseUserName] + CAST([ca3].[MaxNumeric] + DENSE_RANK() OVER (PARTITION BY [b1].[BaseUserName] ORDER BY [b1].[ID]) AS VARCHAR(30)) AS NewUserName
FROM
[base] AS [b1]
CROSS APPLY (SELECT * FROM [UserMax] AS [um] WHERE [um].[BaseUserName] = [b1].[BaseUserName])ca3(BaseUserName,MaxNumeric)
WHERE
[b1].[UserName] = ''
)
UPDATE [t] SET
[USERNAME] = [uv].[NewUserName]
FROM
[#Temp] AS [t]
INNER JOIN [UpdatingValues] AS [uv]
ON ([t].[ID] = [uv].[ID]);
GOAmazing......
Thank you so much, You save my days.
Appreciate !!!
But do you understand what the code is doing? You will have to support this code if you use it in production.
March 7, 2018 at 8:41 pm
Yes, I understand completely. Took an hours to get it.
Will break in temp tables and do the updates.
My next step is open up for all records, will test it first.
New think I have learn is dense_Rank.
Again thank you so much. Much Appriciate .
March 8, 2018 at 7:47 am
Jeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
March 8, 2018 at 7:53 am
roger.plowman - Thursday, March 8, 2018 7:47 AMJeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
So ironic too. Developers say this over and over about users. Yet when developers insist on "designing" tables without bothering with a true logical design, with no real normalization, with "automatic identity clustering keys", etc., and data modelers or DBAs try to call them out on it, they insist that, even with no full-time professional experience as data modelers, that they are as good (really better!) than the dms and DBAs.
In short, when we say, "that's what happens when developers do the data/table design."
This is not said just to be difficult. I find it somewhat insulting and condescending when the implication is made that even after spending thousands of hours, full-time professionally dedicated to data modeling / design, to be told by others with no full-time experience, who did it as a minor subset of their programming, that they're just as skilled as you are.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2018 at 8:37 am
ScottPletcher - Thursday, March 8, 2018 7:53 AMroger.plowman - Thursday, March 8, 2018 7:47 AMJeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
So ironic too. Developers say this over and over about users. Yet when developers insist on "designing" tables without bothering with a true logical design, with no real normalization, with "automatic identity clustering keys", etc., and data modelers or DBAs try to call them out on it, they insist that, even with no full-time professional experience as data modelers, that they are as good (really better!) than the dms and DBAs.
In short, when we say, "that's what happens when developers do the data/table design."This is not said just to be difficult. I find it somewhat insulting and condescending when the implication is made that even after spending thousands of hours, full-time professionally dedicated to data modeling / design, to be told by others with no full-time experience, who did it as a minor subset of their programming, that they're just as skilled as you are.
True enough. However, one nitpick. Nothing wrong with using identities as a clustered and primary key. Mind you, my experience is designing "small to medium" OLTP systems (read, around 10 million records per table) so I can't speak to designing the true behemoths (1+ billion records), which I'm sure have far more severe performance constraints.
I'm fully in agreement about normalization, though. The impedance mismatch between object oriented design and 3NF is often painful (3NF is viewed by many developers as horrifically antiquated). Part of that is undoubtedly the black-box view developers hold toward databases, but you'd think they'd be aware of SPOT as the data equivalent of DRY.
It doesn't help when developers develop against "play" tables with a few dozen records either...and wonder why production performance is measured in geological eras...
"Hey, you're the DBA! The database is the problem, fix it!" (rolling eyes)
It's a pity that as a Lone Wolf I don't have that luxury! 😀
March 8, 2018 at 9:14 am
roger.plowman - Thursday, March 8, 2018 8:37 AMScottPletcher - Thursday, March 8, 2018 7:53 AMroger.plowman - Thursday, March 8, 2018 7:47 AMJeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
So ironic too. Developers say this over and over about users. Yet when developers insist on "designing" tables without bothering with a true logical design, with no real normalization, with "automatic identity clustering keys", etc., and data modelers or DBAs try to call them out on it, they insist that, even with no full-time professional experience as data modelers, that they are as good (really better!) than the dms and DBAs.
In short, when we say, "that's what happens when developers do the data/table design."This is not said just to be difficult. I find it somewhat insulting and condescending when the implication is made that even after spending thousands of hours, full-time professionally dedicated to data modeling / design, to be told by others with no full-time experience, who did it as a minor subset of their programming, that they're just as skilled as you are.
True enough. However, one nitpick. Nothing wrong with using identities as a clustered and primary key. Mind you, my experience is designing "small to medium" OLTP systems (read, around 10 million records per table) so I can't speak to designing the true behemoths (1+ billion records), which I'm sure have far more severe performance constraints.
I'm fully in agreement about normalization, though. The impedance mismatch between object oriented design and 3NF is often painful (3NF is viewed by many developers as horrifically antiquated). Part of that is undoubtedly the black-box view developers hold toward databases, but you'd think they'd be aware of SPOT as the data equivalent of DRY.
It doesn't help when developers develop against "play" tables with a few dozen records either...and wonder why production performance is measured in geological eras...
"Hey, you're the DBA! The database is the problem, fix it!" (rolling eyes)
It's a pity that as a Lone Wolf I don't have that luxury! 😀
Care to tell us what SPOT means? I know what DRY (don't repeat yourself) is. It is a pet peeve when people use acronyms without bothering to tell anyone what it means, in other words assuming that everyone already does.
March 8, 2018 at 10:15 am
Lynn Pettis - Thursday, March 8, 2018 9:14 AMroger.plowman - Thursday, March 8, 2018 8:37 AMScottPletcher - Thursday, March 8, 2018 7:53 AMroger.plowman - Thursday, March 8, 2018 7:47 AMJeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
So ironic too. Developers say this over and over about users. Yet when developers insist on "designing" tables without bothering with a true logical design, with no real normalization, with "automatic identity clustering keys", etc., and data modelers or DBAs try to call them out on it, they insist that, even with no full-time professional experience as data modelers, that they are as good (really better!) than the dms and DBAs.
In short, when we say, "that's what happens when developers do the data/table design."This is not said just to be difficult. I find it somewhat insulting and condescending when the implication is made that even after spending thousands of hours, full-time professionally dedicated to data modeling / design, to be told by others with no full-time experience, who did it as a minor subset of their programming, that they're just as skilled as you are.
True enough. However, one nitpick. Nothing wrong with using identities as a clustered and primary key. Mind you, my experience is designing "small to medium" OLTP systems (read, around 10 million records per table) so I can't speak to designing the true behemoths (1+ billion records), which I'm sure have far more severe performance constraints.
I'm fully in agreement about normalization, though. The impedance mismatch between object oriented design and 3NF is often painful (3NF is viewed by many developers as horrifically antiquated). Part of that is undoubtedly the black-box view developers hold toward databases, but you'd think they'd be aware of SPOT as the data equivalent of DRY.
It doesn't help when developers develop against "play" tables with a few dozen records either...and wonder why production performance is measured in geological eras...
"Hey, you're the DBA! The database is the problem, fix it!" (rolling eyes)
It's a pity that as a Lone Wolf I don't have that luxury! 😀
Care to tell us what SPOT means? I know what DRY (don't repeat yourself) is. It is a pet peeve when people use acronyms without bothering to tell anyone what it means, in other words assuming that everyone already does.
| SPOT | Système Pour l'Observation de la Terre (French remote sensing satellite) |
| SPOT | Satellite Personal Tracker |
| SPOT | Spotlight |
| SPOT | Shared Product Object Tree |
| SPOT | Smart Personal Object Technology (Microsoft) |
| SPOT | Single Point of Truth |
| SPOT | Screening of Passengers by Observation Techniques |
| SPOT | Satellite Probatoire d'Observation de la Terre |
| SPOT | Sensor Placement Optimization Tool (drinking water research) |
| SPOT | Synchronized Predeployment and Operational Tracker (US Department of Defense program for trackingdeployed contractors) |
| SPOT | Special Purpose Operational Training (airlines) |
| SPOT | Single Payment Options Trading (securities) |
| SPOT | Synchronized Pre-Deployment and Operational Tracker (bar-code system used to track travel/movement ofDoD contractors) |
| SPOT | Sequential Proxy Optimization Technique |
| SPOT | Secondary-Location Point of Termination |
| SPOT | Sociedad Puertorriqueña de Ortopedia y TraumatologÃa |
| SPOT | Searching Peak of Tension (polygraph testing) |
| SPOT | Stupid Previous Owner Trick (automotive) |
| SPOT | Sensor Placement Orientation Tool |
| SPOT | Simply Protecting Our Tots |
| SPOT | Space Station Proximity Operations Trainer |
| SPOT | Speed Position and Track |
| SPOT | Sprint Procurement Online Tool |
| SPOT | Signaling Point of Termination |
| SPOT | Strategic Programs of Technology (Sprint) |
| SPOT | Swiss Professional Officer Training Course |
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 8, 2018 at 10:17 am
True enough. However, one nitpick. Nothing wrong with using identities as a clustered and primary key. Mind you, my experience is designing "small to medium" OLTP systems (read, around 10 million records per table) so I can't speak to designing the true behemoths (1+ billion records), which I'm sure have far more severe performance constraints.
Roger.Plowman
The clustered index is the single most important overall performance factor for that table. Thus, it is wrong to just slap an identity on (nearly) every table and automatically make that the clustering key. Major mistake. And it's often done far too early in the design process, corrupting that process. Candidate keys are never determined and evaluated -- why bother, when you've already determined the "key"? But you can't verify that data within a table directly relates to "the key, the whole key and nothing but the key" if the key is just some meaningless number!
Now, there is nothing wrong with having a "sequential number" in the logical design as the identifying attribute of an entity. For example, a customer number (since it's easy to see that customer name nor email address is a unique or permanent key). But it is not an "identity", because that is an implementation method, not a logical design feature. The seq value could come from a sequence, or a pre-filled table of values, or any other number of ways. That need not be determined at the logical phase, only that a seq num will be the identifier for that entity.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2018 at 10:20 am
Michael L John - Thursday, March 8, 2018 10:15 AMLynn Pettis - Thursday, March 8, 2018 9:14 AMroger.plowman - Thursday, March 8, 2018 8:37 AMScottPletcher - Thursday, March 8, 2018 7:53 AMroger.plowman - Thursday, March 8, 2018 7:47 AMJeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
So ironic too. Developers say this over and over about users. Yet when developers insist on "designing" tables without bothering with a true logical design, with no real normalization, with "automatic identity clustering keys", etc., and data modelers or DBAs try to call them out on it, they insist that, even with no full-time professional experience as data modelers, that they are as good (really better!) than the dms and DBAs.
In short, when we say, "that's what happens when developers do the data/table design."This is not said just to be difficult. I find it somewhat insulting and condescending when the implication is made that even after spending thousands of hours, full-time professionally dedicated to data modeling / design, to be told by others with no full-time experience, who did it as a minor subset of their programming, that they're just as skilled as you are.
True enough. However, one nitpick. Nothing wrong with using identities as a clustered and primary key. Mind you, my experience is designing "small to medium" OLTP systems (read, around 10 million records per table) so I can't speak to designing the true behemoths (1+ billion records), which I'm sure have far more severe performance constraints.
I'm fully in agreement about normalization, though. The impedance mismatch between object oriented design and 3NF is often painful (3NF is viewed by many developers as horrifically antiquated). Part of that is undoubtedly the black-box view developers hold toward databases, but you'd think they'd be aware of SPOT as the data equivalent of DRY.
It doesn't help when developers develop against "play" tables with a few dozen records either...and wonder why production performance is measured in geological eras...
"Hey, you're the DBA! The database is the problem, fix it!" (rolling eyes)
It's a pity that as a Lone Wolf I don't have that luxury! 😀
Care to tell us what SPOT means? I know what DRY (don't repeat yourself) is. It is a pet peeve when people use acronyms without bothering to tell anyone what it means, in other words assuming that everyone already does.
SPOT Système Pour l'Observation de la Terre (French remote sensing satellite) SPOT Satellite Personal Tracker SPOT Spotlight SPOT Shared Product Object Tree SPOT Smart Personal Object Technology (Microsoft) SPOT Single Point of Truth SPOT Screening of Passengers by Observation Techniques SPOT Satellite Probatoire d'Observation de la Terre SPOT Sensor Placement Optimization Tool (drinking water research) SPOT Synchronized Predeployment and Operational Tracker (US Department of Defense program for trackingdeployed contractors) SPOT Special Purpose Operational Training (airlines) SPOT Single Payment Options Trading (securities) SPOT Synchronized Pre-Deployment and Operational Tracker (bar-code system used to track travel/movement ofDoD contractors) SPOT Sequential Proxy Optimization Technique SPOT Secondary-Location Point of Termination SPOT Sociedad Puertorriqueña de Ortopedia y TraumatologÃa SPOT Searching Peak of Tension (polygraph testing) SPOT Stupid Previous Owner Trick (automotive) SPOT Sensor Placement Orientation Tool SPOT Simply Protecting Our Tots SPOT Space Station Proximity Operations Trainer SPOT Speed Position and Track SPOT Sprint Procurement Online Tool SPOT Signaling Point of Termination SPOT Strategic Programs of Technology (Sprint) SPOT Swiss Professional Officer Training Course
See what I mean? I am guessing, after reading all of these, that in this context SPOT is supposed to mean Single Point Of Truth.
March 8, 2018 at 10:24 am
'm fully in agreement about normalization, though. The impedance mismatch between object oriented design and 3NF is often painful (3NF is viewed by many developers as horrifically antiquated).
Roger.Plowman
This is an interesting issue. I'll just point out that developers often ignore the fact that:
THE CODE IS WHAT IS TRANSIENT, AND WILL BE SUPPLANTED SOON ENOUGH, THE DATA LIVES FOREVER!
Go back 20 years. Is it the same code as today? Not likely. But s it the same core data as today? For the most part, yes.
Therefore, my view is that you don't corrupt the data design to match current programming methods. The data needs to be resilient enough to go from COBOL (thrown in just for Celko) to System R to Oracle to whatever. Other methods need to be used to model data for NoSQL and network dbs, but the overall thought is the same. The current method of developing code should not interfere with design the data repository, which will outlive the code anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2018 at 10:50 am
Lynn Pettis - Thursday, March 8, 2018 9:14 AMroger.plowman - Thursday, March 8, 2018 8:37 AMScottPletcher - Thursday, March 8, 2018 7:53 AMroger.plowman - Thursday, March 8, 2018 7:47 AMJeff Moden - Wednesday, March 7, 2018 2:14 PMyogi123 - Wednesday, March 7, 2018 2:04 PMJeff Moden - Wednesday, March 7, 2018 1:58 PMyogi123 - Wednesday, March 7, 2018 1:22 PMMichael L John - Wednesday, March 7, 2018 1:15 PMWhat have you tried? Does it work?
What are the rules for multiple users with the same name?
In your example, James Doyel appears three times. His name becomes James.Doyel1, 2, and 3.How do you know when a second person with the same name may be added?
For example, "Michael.John" is created as a user name. Two weeks later, another Michael John needs to be added to the system. Does the original get changed to Michael.John1, and the new persons user name becomes Michael.John2?Yes,
If Michael.John user name is created, week later same another Michael.John came then it will be Michael.John1..
The Rule is, If user name is not there then it uses as FirstName.LastName. If User Name is there it need to add auto incremented by 1.
Why not just add the user "ID" to everyone and call it a day?
They are going to display on UI, so they need to have unique UserName, and they decide to increment by 1 if it exist.
Heh... that's what happens when users do the design. 😀
That's mean--but SO true... :hehe:
So ironic too. Developers say this over and over about users. Yet when developers insist on "designing" tables without bothering with a true logical design, with no real normalization, with "automatic identity clustering keys", etc., and data modelers or DBAs try to call them out on it, they insist that, even with no full-time professional experience as data modelers, that they are as good (really better!) than the dms and DBAs.
In short, when we say, "that's what happens when developers do the data/table design."This is not said just to be difficult. I find it somewhat insulting and condescending when the implication is made that even after spending thousands of hours, full-time professionally dedicated to data modeling / design, to be told by others with no full-time experience, who did it as a minor subset of their programming, that they're just as skilled as you are.
True enough. However, one nitpick. Nothing wrong with using identities as a clustered and primary key. Mind you, my experience is designing "small to medium" OLTP systems (read, around 10 million records per table) so I can't speak to designing the true behemoths (1+ billion records), which I'm sure have far more severe performance constraints.
I'm fully in agreement about normalization, though. The impedance mismatch between object oriented design and 3NF is often painful (3NF is viewed by many developers as horrifically antiquated). Part of that is undoubtedly the black-box view developers hold toward databases, but you'd think they'd be aware of SPOT as the data equivalent of DRY.
It doesn't help when developers develop against "play" tables with a few dozen records either...and wonder why production performance is measured in geological eras...
"Hey, you're the DBA! The database is the problem, fix it!" (rolling eyes)
It's a pity that as a Lone Wolf I don't have that luxury! 😀
Care to tell us what SPOT means? I know what DRY (don't repeat yourself) is. It is a pet peeve when people use acronyms without bothering to tell anyone what it means, in other words assuming that everyone already does.
SPOT = Single Point Of Truth (i.e. only store a piece of data in one place)
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply