Interesting Database Field



  • (So, this "Sidebar" is for WTFs that aren't front-page-worthy.  I'm not sure what I'm posting here is even Sidebar-worthy... but I got a chuckle... maybe you will too.)

    I found this gem of a field in a database table for storing users' names:

     

    MiddleInitial NVARCHAR(255)


  • Made me snort == definitely sidebar worthy.

     



  • I rolled my eyes at first, but then I realised; the column's slightly misnamed. Were it MiddleInitials, then it makes sense, since people can have as many middle names as they like.



  • I think this is the datatype used when you import string data from excel.  Maybe the table was first created from an excel import, and the designer missed changing the datatype for this column.



  • @m0ffx said:

    I rolled my eyes at first, but then I realised; the column's slightly misnamed. Were it MiddleInitials, then it makes sense, since people can have as many middle names as they like.

    If you have 255 middle names then I am going to punch you in the snoot for being a pompous git.



  • @asuffield said:

    @m0ffx said:

    I rolled my eyes at first, but then I realised; the column's slightly misnamed. Were it MiddleInitials, then it makes sense, since people can have as many middle names as they like.

    If you have 255 middle names then I am going to punch you in the snoot for being a pompous git.

    Actually, in most cases, it's the parents who don that absurd amount of middle names upon their child. So I would shoot the parents for being twits and giving their child hundreds of middle names.



  • We're having to deal with data from someone else's program, and one of their fields is a text field for ip address, which is 32 characters long.  Yeah, sometimes people are just not thinking very well...



  • @Lingerance said:

    So I would shoot the parents for being twits and giving their child hundreds of middle names.

    No parents, and even more names?!?



  • Well, not everybody knows how to store them properly



  • I think the person who created the table just selected a string datatype and forgot to adjust the length.



  • @femalegamer said:

    We're having to deal with data from someone else's program, and one of their fields is a text field for ip address, which is 32 characters long.  Yeah, sometimes people are just not thinking very well...

     

    Er, IPV6????


     


  • Discourse touched me in a no-no place

    @valerion said:

    @femalegamer said:
    We're having to deal with data from someone else's program, and one of their fields is a text field for ip address, which is 32 characters long.  Yeah, sometimes people are just not thinking very well...

    Er, IPV6????

    'Text field' would suggest 'human readable form' making 32 characters insufficient. To pluck an example address off Wikipedia:

    2001:0db8:85a3:08d3:1319:8a2e:0370:7344

    I count 39 characters there. 



  • That code is bad. It will be the first code against the wall when UCS-2048 comes.



  • @PJH said:

    'Text field' would suggest 'human readable form' making 32 characters insufficient. To pluck an example address off Wikipedia:

    2001:0db8:85a3:08d3:1319:8a2e:0370:7344

    I count 39 characters there.

    And exactly seven of them are colons.



  • What the hell, I accidentally hit "quote" instead of "edit", and now I can't delete the resulting mess...


  • Discourse touched me in a no-no place

    @magetoo said:

    @PJH said:

    'Text field' would suggest 'human readable form' making 32 characters insufficient. To pluck an example address off Wikipedia:

    2001:0db8:85a3:08d3:1319:8a2e:0370:7344

    I count 39 characters there.

    And exactly seven of them are colons.

    Removing them no longer produces a human readable address.

    Or are you suggesting complicating matters by removing them on insertion to the database, and adding them back in on extraction?



  • @PJH said:

    @magetoo said:
    @PJH said:
    2001:0db8:85a3:08d3:1319:8a2e:0370:7344
    I count 39 characters there.

    And exactly seven of them are colons.

    Removing them no longer produces a human readable address.

    Or are you suggesting complicating matters by removing them on insertion to the database, and adding them back in on extraction?

    Don't ask me, ask whoever decided the field should be 32 characters. I'm just saying that an IPv6 address would fit in there, whether you think it "suggests human readable" or not. (To me, the human readable format is "host.example.com" and nothing else.)

    Of course making it, say, a nice even 40 characters would be better, to guard against all permutations.



  • @magetoo said:

    @PJH said:
    @magetoo said:
    @PJH said:
    2001:0db8:85a3:08d3:1319:8a2e:0370:7344
    I count 39 characters there.

    And exactly seven of them are colons.

    Removing them no longer produces a human readable address.

    Or are you suggesting complicating matters by removing them on insertion to the database, and adding them back in on extraction?

    Don't ask me, ask whoever decided the field should be 32 characters. I'm just saying that an IPv6 address would fit in there, whether you think it "suggests human readable" or not. (To me, the human readable format is "host.example.com" and nothing else.)

     

    Of course making it, say, a nice even 40 characters would be better, to guard against all permutations.

    It's best to put ip addresses into a binary representation in the database, because the translations to human-readable are trivial.  IPv4 can do it with an unsigned int.  (At least in MySQL), searching for values BETWEEN two numbers is highly optimized. 

    But I bet you all already knew that.  Considering that, I dunno why I said it.



  • @magetoo said:

    PJH:
    magetoo:
    PJH:
    2001:0db8:85a3:08d3:1319:8a2e:0370:7344
    I count 39 characters there.

    And exactly seven of them are colons.

     

    Removing them no longer produces a human readable address.

     

    Or are you suggesting complicating matters by removing them on insertion to the database, and adding them back in on extraction?

    Don't ask me, ask whoever decided the field should be 32 characters. I'm just saying that an IPv6 address would fit in there, whether you think it "suggests human readable" or not. (To me, the human readable format is "host.example.com" and nothing else.)

    Of course making it, say, a nice even 40 characters would be better, to guard against all permutations.

    Since it's permissible to omit leading zeroes, and even collapse entire blocks of consecutive groups of zeroes in IPv6, can you please do me the honour of properly formatting this address?

    23D4456EA67822

    While adding the colons in an IPv6 address is unneccesary if you *require* all 32 digits to be included in a field such as this, it just may be worth the extra 7 bits to ensure that you're getting the full picture, all the time (since, by the rationale that this field may share IPv4 and IPv6 addresses, you never know how many digits you'd receive).

    Your recommendation of going with a 40-char field would be a far-better solution.

    /just sayin'

    P.S. if you guessed 0002:03D4:456E:0000:0000:00A6:0782:0002, you win!



  • @caffeinatedbacon said:

    23D4456EA67822

    P.S. if you guessed 0002:03D4:456E:0000:0000:00A6:0782:0002, you win!

    No. It's ::0023:d445:6ea6:7822. ipv6 collapsed form is not ambiguous, any more than ipv4 collapsed form is.



  • @asuffield said:

    @caffeinatedbacon said:

    23D4456EA67822

    P.S. if you guessed 0002:03D4:456E:0000:0000:00A6:0782:0002, you win!

    No. It's ::0023:d445:6ea6:7822. ipv6 collapsed form is not ambiguous, any more than ipv4 collapsed form is.

    Actually, you would be incorrect. You may collapse any leading zero in any group of four hexidecimal groups in IPv6, *as well as* any consecutive group of four zeroes (but only one set of consecutive groups of zeroes may be collapsed), so the example provided could be ::0023:d445:6ea6:7822 or it could be 0023:d445:6ea6:7822::, which of course, as you can see, are two vastly different addresses.

    As for the specific case I was citing, please scroll up through the assumptions above.

    femalegamer said that she was dealing with a 32-character field for an IP address.
    valerion replied "IPv6?", assuming that femalegamer hadn't considered the possibility that it was to house an IPv6 address
    PJH pointed out that a properly formatted IPv6 address features colons separating 16-bit groups
    magetoo seemed to think that 32 Characters were enough to handle an IPv6 address, which PJH rebutted
    magetoo again, stated that an IPv6 address would fit there, but that perhaps a 40 Char string would be better

    In my post, I attempted to highlight that the only way you could accurately reflect an IP address in a field that mixed IPv4 & IPv6 addresses was with a field size that allowed the inclusion of all required seperators and humorously highlighting the challenge of re-inserting those seperators in a variable-length field; I agreed with magetoo's latter suggestion of instead making it a 40-character field (though 39 would do) to overcome that challenge. I made no mention in my post related to the (non)ambiguity of a *properly-formatted* collapsed-form IPv6 address, but did allude to the fact that a 32 character field would prohibit you, strictly-speaking, from properly formatting every possible IPv6 address received.

    Regardless of the semantics in my post, or those previous, can you think of a valid reason to arbitrarily extended a 32-bit address to 32 characters, or restrict a 128-bit address with it's required seperators, to 32 characters? Would you seriously be willing to deal with the headache of programming countless exceptions for the sake of 7 bits/record?

     In my part of the world, that does != success.



  • @caffeinatedbacon said:

    so the example provided could be ::0023:d445:6ea6:7822 or it could be 0023:d445:6ea6:7822::

    No. It's defined as being ::0023:d445:6ea6:7822. Bare numbers without colons build up from the low byte, just like ipv4 (where the address "16" is 0.0.0.16, not 16.0.0.0).

    @caffeinatedbacon said:

    You may collapse any leading zero in any group of four hexidecimal groups in IPv6, *as well as* any consecutive group of four zeroes (but only one set of consecutive groups of zeroes may be collapsed)

    If AND ONLY IF you are using colon notation, which you were not. Furthermore, any consecutive groups of zeroes may be collapsed only into ::, not into nothing.

     

    You can't just make up your own ways to write addresses. There are very clearly defined rules for how ipv6 notation works. 



  • @asuffield said:

    No. It's defined as being ::0023:d445:6ea6:7822. Bare numbers without colons build up from the low byte, just like ipv4 (where the address "16" is 0.0.0.16, not 16.0.0.0).
    Bare numbers without colons do not build into anything. Building up from the low byte is not an acceptable addressing method specified in RFC 4291, bare numbers without colons are not properly formatted IPv6 addresses and should not be assumed to be anything. Case in point, what IP address does '45' represent (using your bare numbers can be assumed to build up from the low byte argument)? Is it an IPv4 decimal notation 45 (binary 00101101) or an IPv6 Hex 45 (1000101)? The whole point I was trying to make is that if you do not allow for a full, properly-formatted address in a field that assumes you're collecting mixed IP address data, you're capturing garbage.

    @asuffield said:

    If AND ONLY IF you are using colon notation, which you were not. Furthermore, any consecutive groups of zeroes may be collapsed only into ::, not into nothing.

    You can't just make up your own ways to write addresses. There are very clearly defined rules for how ipv6 notation works. 

    No kidding. Well I guess I've clearly been schooled.

    Dude, go back and read my last post, take a breath, and get a life.



  • @caffeinatedbacon said:

    @asuffield said:

    No. It's defined as being ::0023:d445:6ea6:7822. Bare numbers without colons build up from the low byte, just like ipv4 (where the address "16" is 0.0.0.16, not 16.0.0.0).
    Bare numbers without colons do not build into anything. Building up from the low byte is not an acceptable addressing method specified in RFC 4291, bare numbers without colons are not properly formatted IPv6 addresses and should not be assumed to be anything. Case in point, what IP address does '45' represent (using your bare numbers can be assumed to build up from the low byte argument)? Is it an IPv4 decimal notation 45 (binary 00101101) or an IPv6 Hex 45 (1000101)? The whole point I was trying to make is that if you do not allow for a full, properly-formatted address in a field that assumes you're collecting mixed IP address data, you're capturing garbage.

    @asuffield said:

    If AND ONLY IF you are using colon notation, which you were not. Furthermore, any consecutive groups of zeroes may be collapsed only into ::, not into nothing.

    You can't just make up your own ways to write addresses. There are very clearly defined rules for how ipv6 notation works. 

    No kidding. Well I guess I've clearly been schooled.

    Dude, go back and read my last post, take a breath, and get a life.


    Use the tag, Luke.



  • @MasterPlanSoftware said:

    Use the tag, Luke.

    LOL, I'm a long-time reader of TDWTF, but don't spend a lot of time in the forums so until now, hadn't had the joy of meeting asuffield; I've now read a pile of his posts thanks to that tag and have to admit, he seems quite brillant!

    Thanks, MasterPlanSoftware, it's nice to have have someone bring some sense to a thread. Cheers!



  • Apologies for the grave digging, but since it was adressed (ha!) to me...
    @caffeinatedbacon said:

    Since it's permissible to omit leading zeroes, and even collapse entire blocks of consecutive groups of zeroes in IPv6, can you please do me the honour of properly formatting this address?

    23D4456EA67822

    Ahem...
    Building up from the low byte is not an acceptable addressing method specified in RFC 4291, bare numbers without colons are not properly formatted IPv6 addresses and should not be assumed to be anything.

    (emphasis mine)

    I suppose the answer to your request would be "that's not a valid address", then.

    P.S. if you guessed 0002:03D4:456E:0000:0000:00A6:0782:0002, you win!

    And I'd also suggest that 000203D4456E0000000000A607820002 really does fit in a 32-character field, even if it's pretty stupid to do it that way.



  • @magetoo said:

    Apologies for the grave digging, but since it was addressed (ha!) to me...

    I think it's only fair :)

    @caffeinatedbacon said:

    Since it's permissible to omit leading zeroes, and even collapse entire blocks of consecutive groups of zeroes in IPv6, can you please do me the honour of properly formatting this address?

    23D4456EA67822

    OK, granted it was a pretty fuzzy/crappy way of explaining my point, but this is what I was thinking when I tried to capture problems that could arise with that particular field length:

    An unsuspecting user loads a DB with IP's from a CSV file.
    The IP field has properly-formatted IPv6 addresses (with leading zeroes and consecutive zero word-groups collapsed)
    The user tries to copy full addresses that exceed 32 chars and hit errors at that point

    The user has two options at this point: either do a considerable amount of clean-up in their source data (since they can't just do a simple search and replace of colons with zeroes), or contact someone in dev so they can change the field length on the IP address field. If it were you or me and the field size was really that important, we would do some programming to automate the data clean-up, but for the guy making $12/hr whose job it is to load the data, that task may not be so trivial. In my worst case scenario, I imagined that the data-entry guy would do what was most expedient for him which would be to simply search and replace ":" with "", thereby tucking everything to 32 chars or less, solving his problem but creating a hidden disaster for future DB users.

    So you know, I really wasn't trying to pick on your post, especially since you were just offering a possible path of reasoning in the twisted mind of some other anonymous programmer, and who knows, maybe that's what he was thinking (or it could be something as stupid as someone telling him one time that an IPv4 address is 32 bits... and everyone knows that 32 bits = 32 chars, right? ;) I was just trying to offer a view as to why 32 chars really isn't a great solution for containing IPv6 addresses (which you seem to agree with as well).

    Anyway, I hope we can all be friends and carry on (this thread is giving me indigestion ;)

    Cheers!


Log in to reply