April 27, 2011 at 8:31 am
Richard Warr (4/27/2011)
Nice question and an interesting example. Would renaming the columns "Telephone 1" and "Telephone 2" to "Home Phone" and "Mobile Phone" mean that the table was then in 1NF?
No, but you could normalize the Phone number type.
Defining a table to store the repeating types of phone numbers.
You could start by adding another Table called Phone_Number_Types with an Id coulumn.
Then you could use a lookup table to match the repeating Phone_Numbers to the repeating Phone_Number_Types for each Employee.
April 27, 2011 at 8:36 am
Steve Jones - SSC Editor (4/27/2011)
I am trying to get better and have learned to write questions that probe a bit without trying to trick someone.
"without trying to trick someone"
That says it all.
If you are going to tell 70% or more of a community that they are wrong, you should never try to trick them.
Doing this could activate the kind of basic defense mechanisms that are counter productive to learning.
April 27, 2011 at 9:25 am
Nice question, testing our basics.:-)
M&M
April 27, 2011 at 9:26 am
Excellent question, Thanks!
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 27, 2011 at 10:20 am
Nice question, thanks!
April 27, 2011 at 10:26 am
Steve Jones - SSC Editor (4/27/2011)
Richard Warr (4/27/2011)
Nice question and an interesting example. Would renaming the columns "Telephone 1" and "Telephone 2" to "Home Phone" and "Mobile Phone" mean that the table was then in 1NF?I am not the db design guru, but from my understanding, that would be incorrect. The "use" of that property is different, but both home and mobile are the same type of property in relation to the entity.
Steve, I hate to disagree cuz I think you know ALOT more about SQL than I do BUT 😀
These always stir great debates in my classes. I have to disagree that the "home and mobile are the same type of property in relation to the entity". A home phone and a mobile phone have as different a relationship to an employee, for example, as his last name and first name do, some would say. I would agree that Phone1 and Phone2 would violate 1NF because the relationship looks the same, just like using Name1 and Name2 would in place of Firstname and LastName.
So the type of property really is different because one is used to call him at home only and one is used to call him anywhere... IMHO 😉
But a great question Steve!!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 27, 2011 at 10:53 am
I may be wrong, and I'll admit that. However the "use" of the data in terms of when/how you might call someone is debatable. The data domain itself is a contact method for phones, and in that definition they are definitely repeated elements even if home/cell.
I would argue that while you might store one in the person table as something like "work contact", you would not keep the home phone number there as well since many people might have other home contacts (fax, phone 2, work, etc.) In that case, this design might be a "denormalization" to speed up queries, but wouldn't necessarily be part of 1NF.
EmployeeID Name EmergencyPhone
---------- ------- --------------
1 Bob 555-555-1212
2 Bill 555-555-1214
EmployeeID Telephone Type
---------- ------------ ------
1 555-555-1212 Cell
2 555-555-1214 Home
1 555-555-1213 Cell
2 555-555-1216 Home
April 27, 2011 at 11:47 am
Steve Jones - SSC Editor (4/27/2011)
I may be wrong, and I'll admit that. However the "use" of the data in terms of when/how you might call someone is debatable. The data domain itself is a contact method for phones, and in that definition they are definitely repeated elements even if home/cell.I would argue that while you might store one in the person table as something like "work contact", you would not keep the home phone number there as well since many people might have other home contacts (fax, phone 2, work, etc.) In that case, this design might be a "denormalization" to speed up queries, but wouldn't necessarily be part of 1NF.
EmployeeID Name EmergencyPhone
---------- ------- --------------
1 Bob 555-555-1212
2 Bill 555-555-1214
EmployeeID Telephone Type
---------- ------------ ------
1 555-555-1212 Cell
2 555-555-1214 Home
1 555-555-1213 Cell
2 555-555-1216 Home
HaHAAA, yeah, you see, I cannot say you are "wrong". It depends how you define the data domain, so it remains open to interpretation. That's why I love this topic.
That's why I feel that normalization rules are really "mostly guidelines" to quote Barbosa (...."aaarrggghh" 😀 )
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 27, 2011 at 12:08 pm
Richard Warr (4/27/2011)
Nice question and an interesting example. Would renaming the columns "Telephone 1" and "Telephone 2" to "Home Phone" and "Mobile Phone" mean that the table was then in 1NF?
Only if a person only ever has one home number and one mobile number. I had to carry two cell phones for a while, since I had to have a company one for "off-hours support calls", and wasn't allowed to use it for any personal calls. So I had mine, and theirs. Which would you list in that column?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2011 at 12:23 pm
GSquared (4/27/2011)
Richard Warr (4/27/2011)
Nice question and an interesting example. Would renaming the columns "Telephone 1" and "Telephone 2" to "Home Phone" and "Mobile Phone" mean that the table was then in 1NF?Only if a person only ever has one home number and one mobile number. I had to carry two cell phones for a while, since I had to have a company one for "off-hours support calls", and wasn't allowed to use it for any personal calls. So I had mine, and theirs. Which would you list in that column?
Easy. Home, PersonalCell, WorkCell 😀
And each has a different purpose.
It is the same argument as the name normalization discussion.
OK, here is the fix for hardcore 1NF fans. One column, long text datatype (pick your fav) like concatenating an entire name if there is no use for breaking a name down into it's parts.
H7605551212P7605551313W7605551212D7605551515 (H for home, P for personal cell, W for work cell, D for desk, etc.)
Just concatenate them all into one column with letters to identify each portion of the string and an area code of 000 would let you know they do not have such a phone
H7605551212P0000000000W7605551212D7605551515
Just make sure the string is long enough to include every possible phone...
But the business requirement almost ALWAYS has a need for breaking this data down. That is why we talk about normalization, right?
And let me know if any of you adopt this new column....lol... I would love to see your dialing rules to handle that string of data... :w00t:
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 27, 2011 at 12:44 pm
Peter Trast (4/27/2011)
GSquared (4/27/2011)
Richard Warr (4/27/2011)
Nice question and an interesting example. Would renaming the columns "Telephone 1" and "Telephone 2" to "Home Phone" and "Mobile Phone" mean that the table was then in 1NF?Only if a person only ever has one home number and one mobile number. I had to carry two cell phones for a while, since I had to have a company one for "off-hours support calls", and wasn't allowed to use it for any personal calls. So I had mine, and theirs. Which would you list in that column?
Easy. Home, PersonalCell, WorkCell 😀
And each has a different purpose.
It is the same argument as the name normalization discussion.
OK, here is the fix for hardcore 1NF fans. One column, long text datatype (pick your fav) like concatenating an entire name if there is no use for breaking a name down into it's parts.
H7605551212P7605551313W7605551212D7605551515 (H for home, P for personal cell, W for work cell, D for desk, etc.)
Just concatenate them all into one column with letters to identify each portion of the string and an area code of 000 would let you know they do not have such a phone
H7605551212P0000000000W7605551212D7605551515
Just make sure the string is long enough to include every possible phone...
But the business requirement almost ALWAYS has a need for breaking this data down. That is why we talk about normalization, right?
And let me know if any of you adopt this new column....lol... I would love to see your dialing rules to handle that string of data... :w00t:
Woot! I love it! 😀
Actually, because of multiple phone possibilities, I usually break it down into a Phones table, with a type column. I've even gone so far as to set up a many-to-many join to an Entities table (similar to a People table, but allows for companies and other legal "entities" that aren't individual human beings), since in some/many/most cases, one phone can reach multiple people. Other than the many-to-many piece, a vertical table is pretty usual. If it includes a country code, it can internationalize pretty easily too.
But I really like yours because it just hits my sense of humor the right way, and, sometimes, that's the most important aspect to writing code! :w00t:
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2011 at 12:49 pm
Peter Trast (4/27/2011)
Steve Jones - SSC Editor (4/27/2011)
I may be wrong, and I'll admit that. However the "use" of the data in terms of when/how you might call someone is debatable. The data domain itself is a contact method for phones, and in that definition they are definitely repeated elements even if home/cell.I would argue that while you might store one in the person table as something like "work contact", you would not keep the home phone number there as well since many people might have other home contacts (fax, phone 2, work, etc.) In that case, this design might be a "denormalization" to speed up queries, but wouldn't necessarily be part of 1NF.
EmployeeID Name EmergencyPhone
---------- ------- --------------
1 Bob 555-555-1212
2 Bill 555-555-1214
EmployeeID Telephone Type
---------- ------------ ------
1 555-555-1212 Cell
2 555-555-1214 Home
1 555-555-1213 Cell
2 555-555-1216 Home
HaHAAA, yeah, you see, I cannot say you are "wrong". It depends how you define the data domain, so it remains open to interpretation. That's why I love this topic.
That's why I feel that normalization rules are really "mostly guidelines" to quote Barbosa (...."aaarrggghh" 😀 )
"Normalization" is a matter of differering orthodoxies, really. I've seen arguments about whether NULLs should be allowed in "normalized data" that actually included personal insults against anyone disagreeing with a certain point of view. I guess my problem is that I don't have strong feelings about normalization one way or the other. I go with what's most practical for the problem I'm trying to solve. And have been, essentially, told I was a heretic for doing so.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2011 at 12:50 pm
GSquared (4/27/2011)
Richard Warr (4/27/2011)
Nice question and an interesting example. Would renaming the columns "Telephone 1" and "Telephone 2" to "Home Phone" and "Mobile Phone" mean that the table was then in 1NF?Only if a person only ever has one home number and one mobile number. I had to carry two cell phones for a while, since I had to have a company one for "off-hours support calls", and wasn't allowed to use it for any personal calls. So I had mine, and theirs. Which would you list in that column?
It would depend on who I was. If I was your father I would have your personal number. If I was a business associate I'd have your business one. I can think of few scenarios where both would need to be stored (but I'm sure there are some ;-))
April 27, 2011 at 2:46 pm
GSquared (4/27/2011)
Peter Trast (4/27/2011)
Steve Jones - SSC Editor (4/27/2011)
I may be wrong, and I'll admit that. However the "use" of the data in terms of when/how you might call someone is debatable. The data domain itself is a contact method for phones, and in that definition they are definitely repeated elements even if home/cell.I would argue that while you might store one in the person table as something like "work contact", you would not keep the home phone number there as well since many people might have other home contacts (fax, phone 2, work, etc.) In that case, this design might be a "denormalization" to speed up queries, but wouldn't necessarily be part of 1NF.
EmployeeID Name EmergencyPhone
---------- ------- --------------
1 Bob 555-555-1212
2 Bill 555-555-1214
EmployeeID Telephone Type
---------- ------------ ------
1 555-555-1212 Cell
2 555-555-1214 Home
1 555-555-1213 Cell
2 555-555-1216 Home
HaHAAA, yeah, you see, I cannot say you are "wrong". It depends how you define the data domain, so it remains open to interpretation. That's why I love this topic.
That's why I feel that normalization rules are really "mostly guidelines" to quote Barbosa (...."aaarrggghh" 😀 )
"Normalization" is a matter of differering orthodoxies, really. I've seen arguments about whether NULLs should be allowed in "normalized data" that actually included personal insults against anyone disagreeing with a certain point of view. I guess my problem is that I don't have strong feelings about normalization one way or the other. I go with what's most practical for the problem I'm trying to solve. And have been, essentially, told I was a heretic for doing so.
Yeah, I agree. It is a religious discussion for many. I think that the practical approach is asking, "What is the business need?" And not allowing NULLs is absurd and could only really be defended by an academician, because in the real world, "NULLs happen" 🙂 (yes, I just made that up).
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
April 28, 2011 at 3:01 am
What a coincidence. I just read up on 1NF/2NF/3NF because I started working on a presentation to my junior colleagues on the topic last night. Seems like you guys gave me some more food for thought, even on a seemingly simple concept like 1NF.
Anyhow, great back-to-the-basics question, Steve.
Edit: Fixed typo
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply