• nickp (unregistered)

    That's nothing; Wait until you work with a table that has 146 columns, with most of them carrying obsolete or redundant data.

    Why bother with normalization? All those expensive joins!

  • Ziplodocus (unregistered)

    Looks like a whole heap of trouble

  • Squiggle (unregistered) in reply to nickp
    That's nothing; Wait until you work with a table that has 146 columns, with most of them carrying obsolete or redundant data.

    Only 146 columns?

    I used to work on a system that had 2100 columns split across 16 tables which all shared one single primary key.

    We had gems such as USERTABLE3.CUSTOMER_1_CHILD_3_LAST_NAME. At least we had an custom ORM on top of that... which was just a switch statement so long that it had to be split between multiple functions, as it exceeded the maximum VB6 method size.

    Oh gods. The flashbacks are starting again.

    Excuse me.

    (sob)

  • faoileag (unregistered)

    So instead of three tables (FIP_CAI, FIP_INP, FIP_DEG) you just got one. Big deal.

    And not necessarily a wtf. If data from all three tables is routinely used together, why bother with a join or three separate selects each and every time?

    Unless we know the code that works with the data, we can't tell if this represents a wtf.

    And if an ORM comes into play as well the schema is perfectly fine as it will represent the fields of an object. Ok, then the architect of that object might be to blame (composite pattern anyone?), but the poor database schema is innocent.

  • Pete (unregistered)
    ...a progressive nation like France...

    The real WTF

  • solar (unregistered) in reply to faoileag

    I hope this failoeag fellow has no input to database design anywhere

  • Ziplodocus (unregistered) in reply to faoileag
    faoileag:
    So instead of three tables (FIP_CAI, FIP_INP, FIP_DEG) you just got one. Big deal.

    And not necessarily a wtf. If data from all three tables is routinely used together, why bother with a join or three separate selects each and every time?

    Unless we know the code that works with the data, we can't tell if this represents a wtf.

    And if an ORM comes into play as well the schema is perfectly fine as it will represent the fields of an object. Ok, then the architect of that object might be to blame (composite pattern anyone?), but the poor database schema is innocent.

    I hope this is a troll post!

  • faoileag (unregistered)
    Erik wrote:
    A developer could think they're selecting the FirstName column for Users, but it could be for Clients if they read the column name too quickly
    Erm, no. There is no FirstName column in that table, and no user/client.

    Actually, looking at that table schema, you can't make many mistakes selecting from that table, if any.

    The columns are grouped (CAI, INP and DEG) but I haven't seen any duplicate names between groups. If you drop "FIP_group_" the remaining name is unique. And it is descriptive. Not for you perhaps, but definitely for a developer in France (or Quebec, or insert_your_favourite_french_speaking_place_in_the_world).

    And looking at those names I am even more convinced that splitting that table into three would probably make no sense. This is all data describing a single object, something like a museum most likely.

  • faoileag (unregistered) in reply to Ziplodocus
    Ziplodocus:
    faoileag:
    So instead of three tables (FIP_CAI, FIP_INP, FIP_DEG) you just got one. Big deal.

    And not necessarily a wtf. If data from all three tables is routinely used together, why bother with a join or three separate selects each and every time?

    Unless we know the code that works with the data, we can't tell if this represents a wtf.

    And if an ORM comes into play as well the schema is perfectly fine as it will represent the fields of an object. Ok, then the architect of that object might be to blame (composite pattern anyone?), but the poor database schema is innocent.

    I hope this is a troll post!

    Nope, it is not.

    Use an orm on a badly designed object and you will get a badly designed table.

    And whether or not the table represented is badly designed is far from clear. Drop the prefixes (FIP_group_...) and you got a lot of columns. But these columns all have different names and seem all to be attributes of some kind of real-life object, like a museum or so.

    So yes, it's a lot of columns. But that does not inherently mean it makes sense to distribute them over more than one table.

  • gz (unregistered)

    that's what "nosql" was really meant for

  • nobulate (unregistered) 
    CREATE VIEW Client AS
SELECT FIP_CAI_LIBELL1,
FIP_CAI_LIBELLE2,
FIP_CAI_ADRESSE1
...
GO
CREATE VIEW Employee AS
SELECT ...
...

    Real world solutions for impractical data schema's aside, I believe this model is no NOSQL originated.

  • faoileag (unregistered)

    Ok, looking at the columns again, one struck me as being quite interesting: "FIP_DEG_ID_TYPEPATRIMOINE".

    The general meaning of "patrimoine" is "heritage", so this table looks as if it would be describing world heritage sites. Some other columns like "FIP_DEG_DATEPROTECTION" seem to corroborate that interpretation.

    So, not a museum as I first assumed, but still an entity where splitting the data might not make sense.

  • Ziplodocus (unregistered) in reply to faoileag
    faoileag:
    Nope, it is not.

    Use an orm on a badly designed object and you will get a badly designed table.

    And whether or not the table represented is badly designed is far from clear. Drop the prefixes (FIP_group_...) and you got a lot of columns. But these columns all have different names and seem all to be attributes of some kind of real-life object, like a museum or so.

    So yes, it's a lot of columns. But that does not inherently mean it makes sense to distribute them over more than one table.

    Hmmm, I think I took the Erik Gurning (TM) too literally and skipped over the table diagram because of the French names.

    The description doesn't match the image.

    The data type usage IS crazy, though. lat and long data stored in a varchar(255), dates stored in a varchar(255). Why not just store the whole lot in one text column, better yet a blob, ooh, ooh no - XML

  • Goberty Flammis (unregistered)

    In "big data" applications, joins are so expensive that denormalising tables is standard practice.

    I don't know French so I wouldn't know what the table is for, or how big it is, but this is not necessarily a WTF.

  • foo AKA fooo (unregistered)

    "Guillaume's employer, BastilleCo ..." Wait, is this an Erik Gern article? Fuck, yes! Skip over the table, but where's the actual article, I mean the 2000 word embellishment of Guillo's adventures, fighting baguettes vs. cigarettes, and finally beheading this bourgeois data structure?

    So much potential wasted! Maybe there's hope for Erik. :)

  • Zog (unregistered)

    Single table, no normalisation....these days we call that NoSQL and BigData

  • faoileag (unregistered) in reply to Ziplodocus
    Ziplodocus:
    Hmmm, I think I took the Erik Gurning (TM) too literally and skipped over the table diagram because of the French names. The description doesn't match the image.
    Actually, because of the author I paid some extra attention to the data. ;-)

    Looking at the article and the screenshot, it is clear that Erik has no idea whatsoever what the table is about. He probably just saw the pattern ("Oooo, columns grouped by prefixes, bad, bad, must be normalized") and invented a story around it.

    Ziplodocus:
    The data type usage IS crazy, though. lat and long data stored in a varchar(255)
    Nothing wrong with that if what you do with the data is just displaying it. If you want to calculate the distance between two world heritage sites by means of a stored procedure, however...
    Ziplodocus:
    dates stored in a varchar(255
    Same applies here. If this database is just storing information that will be displayed (like http://whc.unesco.org/en/list/) it doesn't matter.
    Ziplodocus:
    Why not just store the whole lot in one text column, better yet a blob, ooh, ooh no - XML
    Would make it much more difficult to search, say, for sites where an audio guide is available.
  • (cs)

    TRWTF is the VARCHAR(255) for FIP_INP_GEOPOSLONG and FIP_INP_GEOPOSLAT. 255 characters is way too much since these fields probably stores things like « quarante-huit virgule huit cinq trois zéro huit degrés nord ».

    But maybe they need ångström-level precision for geoposition ?

  • Minnelli (unregistered)
    they often took lunch together
    That gang of bullies from BastilleCo took my lunch! Give it back!
  • faoileag (unregistered) in reply to foo AKA fooo
    foo AKA fooo:
    "Guillaume's employer, BastilleCo ..." Wait, is this an Erik Gern article? ... Maybe there's hope for Erik. :)
    Or maybe someone found a way to get through to him. He doesn't seem to read the comments to his articles, but someone finally trashed him in a tweet with an explicit "@" for him in it.

    So yeah, perhaps there is hope :)

  • faoileag (unregistered) in reply to VinDuv
    VinDuv:
    TRWTF is the VARCHAR(255) for FIP_INP_GEOPOSLONG and FIP_INP_GEOPOSLAT. 255 characters is way too much since these fields probably stores things like « quarante-huit virgule huit cinq trois zéro huit degrés nord ».

    But maybe they need ångström-level precision for geoposition ?

    http://dev.mysql.com/doc/refman/5.0/en/char.html:
    The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store ... Values in VARCHAR columns are variable-length strings
  • Anonymous Will (unregistered) in reply to faoileag
    faoileag:
    VinDuv:
    TRWTF is the VARCHAR(255) for FIP_INP_GEOPOSLONG and FIP_INP_GEOPOSLAT. 255 characters is way too much since these fields probably stores things like « quarante-huit virgule huit cinq trois zéro huit degrés nord ».

    But maybe they need ångström-level precision for geoposition ?

    http://dev.mysql.com/doc/refman/5.0/en/char.html:
    The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store ... Values in VARCHAR columns are variable-length strings

    There is absolutely no way you're going to need 255 characters for a latitude or a longitude. A dozen is quite enough.

  • nickp (unregistered) in reply to Goberty Flammis

    I don't think 99% of organisations have to worry about "big data" enough to go the NoSQL way.

  • (cs) in reply to faoileag
    faoileag:
    http://dev.mysql.com/doc/refman/5.0/en/char.html:
    The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store ... Values in VARCHAR columns are variable-length strings
    That doesn't mean it actually takes variable space. From experience, both CHAR and VARCHAR are still fixed-length buffers in the DB record, and the only difference is that VARCHARs are not returned padded with spaces.
  • Smug Unix User (unregistered)

    A table with many scenic view. Perfect for France.

  • faoileag (unregistered) in reply to Anonymous Will
    Anonymous Will:
    There is absolutely no way you're going to need 255 characters for a latitude or a longitude. A dozen is quite enough.
    I know. But it doesn't matter, as those 255 characters will not be used.

    Declaring all varchars to be of length 255 may be bad style but has no further implications. Especially not with an explicit column name, that tells what data it stores.

    And of course it is enterprisy. You can pinpoint a position to a hitherto unknown precision of (roughly) 1.1 e-247 m!

  • faoileag (unregistered) in reply to Medinoc
    Medinoc:
    faoileag:
    http://dev.mysql.com/doc/refman/5.0/en/char.html:
    The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store ... Values in VARCHAR columns are variable-length strings
    That doesn't mean it actually takes variable space. From experience, both CHAR and VARCHAR are still fixed-length buffers in the DB record, and the only difference is that VARCHARs are not returned padded with spaces.
    Do they? Then I stand corrected...
  • QJo (unregistered) in reply to VinDuv
    VinDuv:
    TRWTF is the VARCHAR(255) for FIP_INP_GEOPOSLONG and FIP_INP_GEOPOSLAT. 255 characters is way too much since these fields probably stores things like « quarante-huit virgule huit cinq trois zéro huit degrés nord ».

    But maybe they need ångström-level precision for geoposition ?

    If they need to internationalize, they may be using the rest of the 255 characters to store the location in more than one language (in formats along the lines you suggest above).

  • merb (unregistered) in reply to Pete
    Pete:
    ...a progressive nation like France...

    The real WTF

    This...

  • Heisenberg (unregistered) in reply to faoileag
    faoileag:
    And of course it is enterprisy. You can pinpoint a position to a hitherto unknown precision of (roughly) 1.1 e-247 m!
    I'm not so certain.
  • schpeelah (unregistered) in reply to faoileag
    faoileag:
    Do they? Then I stand corrected...
    As a rule, in most relational databases all fields are the same size regardless of content. Having a fixed record size and each column as at a fixed offset is what makes SELECT column FROM table WHERE column2==... execute at a reasonable speed.
  • faoileag (unregistered) in reply to Heisenberg
    Heisenberg:
    faoileag:
    And of course it is enterprisy. You can pinpoint a position to a hitherto unknown precision of (roughly) 1.1 e-247 m!
    I'm not so certain.
    Of course the exact precision will be unknown until observed.
  • Dubstep (unregistered)

    A wonderful example of French stupidity.

  • (cs) in reply to Medinoc
    Medinoc:
    That doesn't mean it actually takes variable space. From experience, both CHAR and VARCHAR are still fixed-length buffers in the DB record, and the only difference is that VARCHARs are not returned padded with spaces.

    Ah, no. CHARs are stored space-padded within the row data but the data for VARCHARs are stored in a separate place. Spaces on the end of CHARs are stripped on retrieval, but not VARCHARs. This is MySQL versions after 5.0.3, but my quick Google suggests this is true for most other DBs too.

    That said, unless I need more I try and keep my VARCHARs 85 and under. More than that uses more overhead. The reason is that 85 UTF-8 characters requires 255 bytes, which is the threshold to jump to 2 byte offsets.

  • nmclean (unregistered) in reply to faoileag
    faoileag:
    Ziplodocus:
    The data type usage IS crazy, though. lat and long data stored in a varchar(255)
    Nothing wrong with that if what you do with the data is just displaying it. If you want to calculate the distance between two world heritage sites by means of a stored procedure, however...
    Ziplodocus:
    dates stored in a varchar(255
    Same applies here. If this database is just storing information that will be displayed (like http://whc.unesco.org/en/list/) it doesn't matter.
    What? No. The consumer of the database should decide how the data is used / displayed, not the designer of the schema. What if I want to calculate distances in the future? What if I want to use a different date format? Storing lat/long as chars may be pragmatic, but for dates it makes no sense -- you already have a built-in data type for that. This would only be acceptable if the data was being pulled from some other non-formatted source (i.e. scraping).
  • Warren (unregistered)

    Ye Codds!

  • someguy (unregistered)

    TRWTF is that Erik Gern didn't split this post into three parts and add lots of unbelievable embellishments.

  • QJo (unregistered) in reply to Dubstep
    Dubstep:
    A wonderful example of French stupidity.

    Ze Frrench arre not stoopeed! Zey arre verree angtellizhong.

  • Drop a couple of gurners, and come up smiling. (unregistered)

    Is Eric Gern on some strong amphetamine or amphetamine derivatives? That'd make a lot of sense.

  • ¯\(°_o)/¯ I DUNNO LOL (unregistered)

    It still needs one more field to store XML data for any field they forgot to put in the schema.

    FIP_ETC_CHANTS_AUTRES: TEXT
  • ¯\(°_o)/¯ I DUNNO LOL (unregistered) in reply to someguy
    someguy:
    TRWTF is that Erik Gern didn't split this post into three parts and add lots of unbelievable embellishments.
    I would have gone with an Inspector Clouseau theme.
  • faoileag (unregistered) in reply to someguy
    someguy:
    TRWTF is that Erik Gern didn't split this post into three parts and add lots of unbelievable embellishments.
    Having left BastilleCo, Guillaume, a young, idealistic developer with enough database knowledge to know that tables with more than 20 columns were evil and in need of being split, started work at Palais de Tuileries S.A., a company specializing in online voting software.

    Palais de Tuileries being a rather patriarchal company with the sole founder being the COE (chief of everything), Guillaume knew he could prosper here because he had never really fitted in with the relaxed atmosphere of "make do" at his former employer, where chiseled-in-granite wisdom he had aquired when studying at the Ecole Polytechnique had not been given the reverence it deserved.

    Palais de Tuileries' boss left it to Guillaume's colleagues to set him up on his first day, with Baptiste being his assigned tutor.

    "And this is your Discourse login data", Baptiste told him. "We do all our discussions and bug-tracking here with Discourse".

    Guillaume left the Palais de Tuileries convent shortly after.

  • Buck (unregistered) in reply to VinDuv

    TRWTF is the VARCHAR(255) for FIP_INP_GEOPOSLONG and FIP_INP_GEOPOSLAT.

    Duplicate columns.

    FIP_INP_GEOPOSLONG: VARCHAR(255) FIP_INP_GEOPOSLAT: VARCHAR(255) FIP_INP_GMAPSLNG: FLOAT FIP_INP_GMAPSLAT: FLOAT

    They apparently needed to store the lat/lon formatted for presentation as well as 'raw' for calculation purposes.

  • OldCoder (unregistered) in reply to Anonymous Will
    Anonymous Will:
    There is absolutely no way you're going to need 255 characters for a latitude or a longitude. A dozen is quite enough.
    Bill Gates? Is that you?
  • Valued Service (unregistered) in reply to faoileag
    faoileag:
    Looking at the article and the screenshot, it is clear that Erik has no idea whatsoever what the table is about. He probably just saw the pattern ("Oooo, columns grouped by prefixes, bad, bad, must be normalized") and invented a story around it.

    If that turns out to be true, and everything else about the article is completely made up...

    This site is worthless to me.

    Nothing wrong with pointing out quirks in a business to support the odd code.

    But to make up quirks that mirror the code just to give substance to the article...

    That kinda pisses me off.

  • Valued Service (unregistered) in reply to OldCoder
    OldCoder:
    Anonymous Will:
    There is absolutely no way you're going to need 255 characters for a latitude or a longitude. A dozen is quite enough.
    Bill Gates? Is that you?

    We need atomic precision for lat and long.

    No way we have GPS that can give us that much accuracy....

    but we need the precision.

  • Miriam (unregistered) in reply to Valued Service
    Valued Service:
    faoileag:
    Looking at the article and the screenshot, it is clear that Erik has no idea whatsoever what the table is about. He probably just saw the pattern ("Oooo, columns grouped by prefixes, bad, bad, must be normalized") and invented a story around it.

    If that turns out to be true, and everything else about the article is completely made up...

    This site is worthless to me.

    Nothing wrong with pointing out quirks in a business to support the odd code.

    But to make up quirks that mirror the code just to give substance to the article...

    That kinda pisses me off.

    And this is why pedantically obeying patterns and pointing out the lack therof is a bad thing!

    I feel kind of bad for Erik, though. It seems like his articles are disliked overwhelmingly often.

  • George (unregistered) in reply to Miriam

    Perhaps it's because his articles are overwhelmingly bad.

  • (cs) in reply to faoileag
    faoileag:
    foo AKA fooo:
    "Guillaume's employer, BastilleCo ..." Wait, is this an Erik Gern article? ... Maybe there's hope for Erik. :)
    Or maybe someone found a way to get through to him. He doesn't seem to read the comments to his articles, but someone finally trashed him in a tweet with an explicit "@" for him in it.

    So yeah, perhaps there is hope :)

    Did they? Have a link? lol

    Given the last story was Don Quixote, I'm surprised we didn't get the Three Musketeers here.

  • Valued Service (unregistered) in reply to Miriam
    Miriam:
    And this is why pedantically obeying patterns and pointing out the lack therof is a bad thing!

    I feel kind of bad for Erik, though. It seems like his articles are disliked overwhelmingly often.

    I don't necessarily dislike the article.

    But if he completely fabricated the fact that everyone sits at the same desks, because of some metaphor comparison to the table.... that would make me immediately dislike the article.

    If the submission was the table only, then that's the article. Mention that it has everything in the one table and that there is only one table, and done.

    If that's not enough content, then combine stories.

    You can get creative with the story or theme it, but only if it's based on true events.

    Don't say someone investigated the previous developer by traveling to his farmhouse if that didn't happen.

    If it did happen, then you can say there were windmill pictures everywhere. That's fine.

Leave a comment on “Multiple Tables!? Why bother?”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article