Stupid Schema Tricks



  • So, our web portal app thingy has a FAQ section, and the questions and answers fed from the database... that way, we can add, subtract or edit questions from a simple back-end admin page.

    Here's the (abridged) query to get the question-and-answer list:

    SELECT
     FAQ.Category,
     Q.QuestionText,
     A.AnswerText
    FROM
     FAQ
    JOIN Q
     ON Q.QuestionID = FAQ.QuestionID
    JOIN A
     ON A.AnswerID = FAQ.AnswerID
    

    For those not versed in SQL, or not inclined to parse this query, here's the nut--

    It's a many-to-may relationship between questions and answers. Each question may have multiple answers, each answer may have multiple questions, answers may be shared among questions, and multiple questions could have the same answer. This structure also allows for questions without answers and answers with questions. It's totally flexible, and totally stupid.

    It's not failure, it's worse than failure!



  • Sounds like it's modeling real life.



  • The thing is, you may know the ultimate question, and you may know the ultimate answer, but you can't know both.



  • @JeffS said:

    For those not versed in SQL, or not inclined to parse this query, here's the nut--

    It's a many-to-may relationship between questions and answers. Each question may have multiple answers, each answer may have multiple questions, answers may be shared among questions, and multiple questions could have the same answer. This structure also allows for questions without answers and answers with questions. It's totally flexible, and totally stupid.

    It's not failure, it's worse than failure!

    Ok, come on here. It might not be optimal, but it's really not bad. As far as many-to-many relationships go:

    The question, "why did the power light turn off" could have many answers. "Because it's unplugged." "Because the power is turned off."


    The answer, "if the above steps fail, reboot your computer" could potentially solve many questions.

    The only combination that doesn't really make sense is to have an answer without any questions associated with it, but that's a minor quirk.

    This isn't "worse than failure." At most, I'd call it slightly odd.



  • I don't see why multiple questions couldn't have the same answer depending on context (or I'm thinking more along the line of a knowledge database type scenario). Some questions could be worded differently or the same 'does not apply here' answer' could apply for multiple scenarios. Unless you like copying-and-pasting between questions and making sure they all stay synced then the solution fits the bill.

    A question having multiple answers also makes sense if the answer from another question is appended since they're it's relevant (such as detailed instructions, or whatever).

    An answer not having a question or a question not having an answer seems like a data integrity problem, though.

    On the other hand, if the site only has one-to-one answers and questions yet the system is built many-to-many then that's just overengineered. heh



  • @frits said:

    Sounds like it's modeling real life.

    @Zimzat said:

    I don't see why multiple questions couldn't have the same answer depending on context (or I'm thinking more along the line of a knowledge database type scenario).

    You guys are a bunch of over-engineering engineers!

    Do you ever surf the web? Do you see FAQs anywhere in your web travels? It's a simple question-and-answer mechanism employed since the early days. This is not Wikipedia or the smegging Microsoft knowledge base, ok? FAQ-- Frequently Asked Questions.

    Q. Do you ship to P.O. boxes? A. No.

    Q. How do I recover my password? A. Follow this link on the Profile page.

    Why must you over-complexify this? You remember that TDWTF a few years back about keeping a biker's hands warm in cold weather?



  • Hey man, this is the internet.  No one agrees with you.  Ever.  Get over it.



  • @JeffS said:

    Do you ever surf the web? Do you see FAQs anywhere in your web travels? It's a simple question-and-answer mechanism employed since the early days. This is not Wikipedia or the smegging Microsoft knowledge base, ok? FAQ-- Frequently Asked Questions.

    That's not the point. The point is that the system you outlined above works for your purpose, yes?

    And as an added bonus, it's suited for a more general knowledge base, if you decide you need one in the future, yes?

    And that "bad" query is a thousand times better than almost any other "bad" query I've seen on this site.

    I stand by my "not a WTF."



  • @JeffS said:

    Why must you over-complexify this? You remember that TDWTF a few years back about keeping a biker's hands warm in cold weather?
    Sure, they could have just had a single table called FAQ with two columns 'Q' and 'A', but if you ever wanted to expand it in the future you would be screwed, so they decided to store questions in one table, answers in another, and link them together in the FAQ table. Nothing wrong with that. 

    I agree with the others, Not a WTF. Rather, a database designed by someone who has done it before rather than a web designers first attempt at SQL. 



  • @frits said:

    Hey man, this is the internet.  No one agrees with you.  Ever.  Get over it.

    I disagree.


  • @Zecc said:

    @frits said:

    Hey man, this is the internet.  No one agrees with you.  Ever.  Get over it.

    I disagree.

    You're right.


  • Considered Harmful

    @Zecc said:

    @frits said:

    Hey man, this is the internet.  No one agrees with you.  Ever.  Get over it.

    I disagree.

    You're both wrong.



  • @blakeyrat said:

    The only combination that doesn't really make sense is to have an answer without any questions associated with it, but that's a minor quirk.
     

    42.



  • @b_redeker said:

    42.
     

    Marvin was able to read the question, but he fucking died.



  • I'd add my vote to "not a WTF".  Perhaps overkill but that's about it.  

     

    @JeffS said:

    This structure also allows for questions without answers and answers with questions.
     

    Can we have an example of some of these unanswered FAQs and, er, unquestioned FQAs?




  •  Actually you can't deduce the relationship from the SQL query alone. What if it's defined as a one-on-one relationship between questions and answers in the database schema? Totally plausable.



  • @dhromed said:

    @b_redeker said:

    42.
     

    Marvin was able to read the question, but he fucking died.

     

    Well he was as old as the Universe. Also he talked to my namesake! (The second M is for Mattress)



  • I just noticed there are also multiple categories, but there is no table for these, so if there is multiple answers to a question or the same question is in multiple categories, thats a WTF in itself. Unless of course the category is an enum thats processed later, but I would expect some kind of text for it and therefore another table.I hope the values are not hardcoded.

    Can we see the schema? I'm guessing not, but it doesn't hurt to try :)



  •  I'm going to assume that there really is need to have all this in the database rather than a flat FAQ file. I don't know the system requirements, so it's perfectly feasible that this is a valid design.

    The many-question-to-one-answer relationship is questionable though. What happens when someone changes the answer to a question? Does the app go through the trouble of checking whether the answer is used elsewhere and then decide whether to change it or create a new one and link the question to that? Does it change the answer for all the linked questions? This sounds a lot like an overly optimized design that someone though would be 'neat' because you can save on database entries.  Maybe the answers to questions are selected from a drop-down somewhere which would fascilitate this design, but this sounds like a UI that's a pain in the ass at best.



  • I don't know what language the FAQ front-end is written in, but generally I find accessing a database easier than trying to parse a flat file. 

    Unless of course you mean just a standard HTML page that someone edits to alter the FAQ entries. But that would be boring to develop, and searching it would suck. 



  • @Mole said:

    Not a WTF. Rather, a database designed by someone who has done it before rather than a web designers first attempt at SQL.

    Nope, it's actually a WTF.

    It's a database designed by some self-proclaimed database guru who read an article about data normalization, instead of a by a developer who's been there before.

    Look, flexibility is good, and future development and maintenance should be considered, but it can be taken too far, which happened in this case.

    I'll give you that a one-to-many relationship between Q and A might be useful instead of a straight one-to-one, but, unless you're TopCod3r, you can't give me reasonable examples for the other possibilities a many-to-many allows.



  • @Mole said:

    Can we see the schema? I'm guessing not, but it doesn't hurt to try :)

    Bwaaaaaaaaaaaaa ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha hah!

    Good one, Mole! Yeah, and I'll also show you the design documents, the entity-relationship diagrams and the unit tests! Then I'll show you the tooth fairy!

    Our project has none of these. There is no schema to see.

    ...schema... lolz....



  • @Mole said:

    Unless of course the category is an enum thats processed later[...]

    Category is VARCHAR(25).

    Is this a WTF yet?



  • @JeffS said:

    @Mole said:

    Not a WTF. Rather, a database designed by someone who has done it before rather than a web designers first attempt at SQL.

    Nope, it's actually a WTF.

    It's a database designed by some self-proclaimed database guru who read an article about data normalization, instead of a by a developer who's been there before.

    Please. I've seen databases designed by "developers who have been there before." They've inevitably shit. Most developers hate SQL, have no clue how relations work, and create shitty database schemas. They treat your expensive high-tech database server as some kind of high-capacity copy of Excel. Not all. But most.

    Data normalization is a good thing. It saves you from having to deal with, for example, the 290-column monstrosity I've been handed at work and told to make sense of. Of the 290 columns, something like 30 are repeated data, several are blank all the way down (12 or so, IIRC), and at least 100 of them could have been easily normalized-away. They're feeding us a 21 GB-per-day file when we need perhaps 2 GB of actual data. I'm sure it was designed by a "developer who's been there before."

    @JeffS said:

    I'll give you that a one-to-many relationship between Q and A might be useful instead of a straight one-to-one, but, unless you're TopCod3r, you can't give me reasonable examples for the other possibilities a many-to-many allows.

    I gave a couple of them near the top of the post. And like I said there, the only combination that doesn't make sense is having an answer with zero questions-- of course if they coded the database correctly, there's a Constraint installed to prevent that.



  • @JeffS said:

    @Mole said:

    Can we see the schema? I'm guessing not, but it doesn't hurt to try :)

    Bwaaaaaaaaaaaaa ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha hah!

    Good one, Mole! Yeah, and I'll also show you the design documents, the entity-relationship diagrams and the unit tests! Then I'll show you the tooth fairy!

    Our project has none of these. There is no schema to see.

    ...schema... lolz....

    Just have the database server print it out.

    I'm starting to think the Real WTF is you... if you didn't know databases have a copy of their own schema. (I mean, think about it: how could the database work otherwise?)



  • @JeffS said:

    @Mole said:

    Unless of course the category is an enum thats processed later[...]

    Category is VARCHAR(25).

    Is this a WTF yet?

    Ah! Now you're definitely venturing into WTF territory. At the very least, it should be NVARCHAR. ;)


  • Discourse touched me in a no-no place

    @JeffS said:

    There is no schema to see.
    Rubbish. mysqldump or whatever equivalent there is for your database product. Just tell it to just spit out the table schemata and not the data.



  • @blakeyrat said:

    Please. I've seen databases designed by "developers who have been there before." They've inevitably shit. Most developers hate SQL, have no clue how relations work, and create shitty database schemas. They treat your expensive high-tech database server as some kind of high-capacity copy of Excel. Not all. But most.

    Please. I've seen databases designed by "professional DBAs" that are inevitably shit. Did I tell you about the customer table that had a separate address table, and the address table had a separate "AddressLine" table (to account for PO box, Suite number, or other delivery information because AddressLine1 and AddressLine2 would violate the holiest-of-holy normalization rules) and the address table had the city in a separate "city" table and the states were in another "state" table, so I had to join like five tables just to get the lousy address. This is good design? Throw in some XML, and we have an enterprise system!

    Thanks to that structure, if Cincinnati, Iowa and Cincinnati, Ohio ever decide to change their names at the same time to the same new name, it's only one database update! And that's savings you can take to the bank!

    Look, I agree a database shouldn't be some stupid data store of opaque varchar blobs. But you have to agree that DBAs can shit out some real crap, too. And my little FAQ example is real crap.

    @blakeyrat said:

    @JeffS said:
    I'll give you that a one-to-many relationship between Q and A might be useful instead of a straight one-to-one, but, unless you're TopCod3r, you can't give me reasonable examples for the other possibilities a many-to-many allows.

    I gave a couple of them near the top of the post. And like I said there, the only combination that doesn't make sense is having an answer with zero questions-- of course if they coded the database correctly, there's a Constraint installed to prevent that.

    Why would I want a single answer shared among multiple questions? (Oooooo, I know... like an answer "No" so if we ever want to change all our "No"s to "Yes"s, it's just one database update!)

    I'm starting to think the WTF is you. Anybody who designs a space shuttle when requirements call for a paper airplane...

    What's that old CS quotation about premature optimization?



  • @blakeyrat said:

    @JeffS said:
    @Mole said:

    Unless of course the category is an enum thats processed later[...]

    Category is VARCHAR(25).

    Is this a WTF yet?

    Ah! Now you're definitely venturing into WTF territory. At the very least, it should be NVARCHAR. ;)

    OK, you owe me a new keyboard. There's hot coffee all over mine now.



  • @PJH said:

    @JeffS said:
    There is no schema to see.
    Rubbish. mysqldump or whatever equivalent there is for your database product. Just tell it to just spit out the table schemata and not the data.

    Assuming constraints, primary keys, foreign keys are defined, and further assuming a sane naming convention, you might be able to decipher the schema.

    Sadly, this database has none of those...



  • @Mole said:

    I don't know what language the FAQ front-end is written in,
     

    English.



  •  @dhromed said:

    @Mole said:
    I don't know what language the FAQ front-end is written in,
    English.
    I just knew someone was going to reply with that, and I kinda guessed it was going to be you.

    @JeffS said:

    Sadly, this database has none of those...
    Aha, it now seeming that it was a thrown together database of an intern. At least the category should have been referenced in another table. Sheesh! 



  • @JeffS said:

    @PJH said:
    @JeffS said:
    There is no schema to see.
    Rubbish. mysqldump or whatever equivalent there is for your database product. Just tell it to just spit out the table schemata and not the data.

    Assuming constraints, primary keys, foreign keys are defined, and further assuming a sane naming convention, you might be able to decipher the schema.

    Sadly, this database has none of those...



  • (borrowed account)

    I don't do database so I guessed with google on a couple. Not intended to offend anyone!

     

     Stupid Schema Tricks Faq:


    Q. Do you ever surf the web?
    A. No.

    Q. Do you see FAQs anywhere in your web travels?
    A. No.

    Q. This is not Wikipedia or the smegging Microsoft knowledge base, ok?
    A. I guess, if that really was a question.

    Q. Why must you over-complexify this?
    A. In the absense of a real answer I'm going to ask a question instead. Why did you over-complexify over complicated?

    Q. You remember that TDWTF a few years back about keeping a biker's hands warm in cold weather?
    A. No, but I imagine it would take a full time 'bitch' or two.

    Q. The point is that the system you outlined above works for your purpose, yes?
    A. I guess, if that really was a question.

    Q. And as an added bonus, it's suited for a more general knowledge base, if you decide you need one in the future, yes?
    A. I guess, if that really was a question.

    Q. Can we have an example of some of these unanswered FAQs and, er, unquestioned FQAs?
    A.

     

    Q.

    A. Yes

     

    Q.

    A. No.

     

    Q. What if it's defined as a one-on-one relationship between questions and answers in the database schema?
    A. No three-way action.

    Q. Can we see the schema?
    A. Only if your psychic or able sighted. Audio versions not available.

    Q. What happens when someone changes the answer to a question?
    A. The question remains the same but the answer changes.

    Q. Does the app go through the trouble of checking whether the answer is used elsewhere and then decide whether to change it or create a new one and link the question to that?
    A. could not find stored procedure 'whydotheykeepaskingthis'.

    Q. Does it change the answer for all the linked questions?
    A. could not find stored procedure 'whydotheykeepaskingthis'.

    Q. Is this a WTF yet?
    A. No.

    Q. how could the database work otherwise?
    A. Magic smoke.

    Q. This is good design?
    A. Dear sir, we have perfectly optimized code. It is 100% the best available.

    Q. Why would I want a single answer shared among multiple questions?
    A. could not find stored procedure 'whydotheykeepaskingthis'.

    Q. What's that old CS quotation about premature optimization?
    A. 'Apply nasal spray liberally.'




  •  @chikinpotpi said:

     

     

    Nice doggy! 

    Probably more intelligent than the person who designed the schema for the FAQ engine :)

    But it's still not a WTF.



  •  I think the quote you're looking for is : "Implementing features you don't need, and probably never will, is the root of all evil."



  • @JeffS said:

    Please. I've seen databases designed by "professional DBAs" that are inevitably shit. Did I tell you about the customer table that had a separate address table, and the address table had a separate "AddressLine" table (to account for PO box, Suite number, or other delivery information because AddressLine1 and AddressLine2 would violate the holiest-of-holy normalization rules) and the address table had the city in a separate "city" table and the states were in another "state" table,!

    That isn't a sensible design. That design just means your "professional DBA" doesn't understand Second Normal or Third Normal form. Tell your boss, have him fired-- he's obviously incompetent.

    If City/State/Zip were in a single table, that design would make sense.

    Look, I'm the first one to call people on over-abstracting data. The first one in the room, I guarantee. When I see it, I'll call you on it. But that FAQ example? That's not it. (This address example is much more of a WTF. You should post it, if you can get a database diagram or at least a schema listing.)

    @JeffS said:

    so I had to join like five tables just to get the lousy address.

    Aw!! Poor baby!!!!

    And to make it worse, the company's cafeteria stopped spoon-feeding you at lunch, too! Now you actually have to lift the spoon to your own mouth like some kind of peasant.

    @JeffS said:

    This is good design?

    No, it's not. That's not in any standard normalized form.

    But when I say "DBA", you must understand that what I mean is an actual DBA, not just some bum off the street who managed to bullshit your obviously retarded HR department into a job position.

    @JeffS said:

    Look, I agree a database shouldn't be some stupid data store of opaque varchar blobs. But you have to agree that DBAs can shit out some real crap, too.

    I'll concede that some people who call themselves DBAs can shit out some real crap.



  • @JeffS said:

    @PJH said:
    @JeffS said:
    There is no schema to see.
    Rubbish. mysqldump or whatever equivalent there is for your database product. Just tell it to just spit out the table schemata and not the data.

    Assuming constraints, primary keys, foreign keys are defined, and further assuming a sane naming convention, you might be able to decipher the schema.

    Sadly, this database has none of those...

    How do you know? You weren't even aware that database servers kept track of their own schema!

    The other point I didn't put into my last reply, but should have, is that I simply have to believe at this point that you know absolutely *nothing* about database servers or database design. I don't think you're qualified to talk about good or bad database design.

    I mean, saying "there is no schema." Complaining about having to join 5 tables in a single query-- joining tables is what relational databases *do*! That is their raison d'être! (Not to say that that address field schema made sense, but complaining about joins is somewhere between "completely missing the point" and "incredibly petty."



  • @blakeyrat said:

    complaining about joins is somewhere between "completely missing the point" and "incredibly petty."

    I don't think it was the joining that he was complaining about, but the 5 tables. Although I have to say, "boo hoo", seeing as I've had to write a query that hits 4 or 5 different databases and probably 20-something tables (never counted). Hooray for normalization!



  • @blakeyrat said:

    Complaining about having to join 5 tables in a single query-- joining tables is what relational databases do! That is their raison d'être! (Not to say that that address field schema made sense, but complaining about joins is somewhere between "completely missing the point" and "incredibly petty."

    Welp, thread over, I guess. I didn't realize there were never any downsides to joins. Thanks for the protip!

    I'm off to refactor my FAQ table and abstracting away the questions so each question has a LetterID and SequenceID which I can use to reconstruct the English text. That way, if the letter A ever changes, I can fix my entire FAQ with one database update!

    Hmm, perhaps I should have a CharacterType field too, with a foreign key to either the letter table or the punctuation table. That would be more flexible!

    Complaining about joins is petty! You heard it here first, folks!



  • @JeffS said:

    @blakeyrat said:
    Complaining about having to join 5 tables in a single query-- joining tables is what relational databases do! That is their raison d'être! (Not to say that that address field schema made sense, but complaining about joins is somewhere between "completely missing the point" and "incredibly petty."

    Welp, thread over, I guess. I didn't realize there were never any downsides to joins. Thanks for the protip!

    Yay strawman.

    Of course there are downsides to joins. But the overhead on joins is so tiny as to be worth it in almost every conceivable situation... so using a join instead of duplicating data should always be the default choice.


  • Discourse touched me in a no-no place

    @toth said:

    Hooray for normalization!
    Well, yes. That's part of the denormalisation process. (In the real world, outside of academia, that is.)



    But before you can decide there's a need for normalisation, you have to denormalise the data to begin with. You don't just go 'well I've denormalised it to 0th normal form, so that's it.' (What appears to have happened here.)



    You go to at least 3rd, then if there's a need, go back up to 2nd or 1st (or, indeed 0th,) for the specific parts of the data that require it for whatever reason.



    The point being you go down first. <hook...>



  • The retrieval of the questions/answers is not the WTF. It must be the entry of the question/answer pairs - I mean, how do you normalize stuff like that? Present a list of existing answers or look for literal matches in code?

    Or does eaches entry form create new QuestionIDs and AnswerIDs, essentially making it a 1-to-1 relation?

    BTW, I totally cried when Marvin died.



  • @JeffS said:

    So, our web portal app thingy has a FAQ section, and the questions and answers fed from the database... that way, we can add, subtract or edit questions from a simple back-end admin page. Here's the (abridged) query to get the question-and-answer list:
    SELECT
     FAQ.Category,
     Q.QuestionText,
     A.AnswerText
    FROM
     FAQ
    JOIN Q
     ON Q.QuestionID = FAQ.QuestionID
    JOIN A
     ON A.AnswerID = FAQ.AnswerID
    

    For those not versed in SQL, or not inclined to parse this query, here's the nut-- It's a many-to-may relationship between questions and answers. Each question may have multiple answers, each answer may have multiple questions, answers may be shared among questions, and multiple questions could have the same answer. This structure also allows for questions without answers and answers with questions. It's totally flexible, and totally stupid. It's not failure, it's worse than failure!

    Without actually seeing the data and knowing the entry format, this may be a perfectly reasonable schema.  Perhaps the multiple answers/questions are used to overcome a field size limit? (Oracle limits varhar to 4k or so characters).  In which case the only way to store a large and complicated question or answer would be to parse it into multiple fields and/or records.  Neither do we have details of what the original request to the developer was.  Also, it looks as though you may have mis-edited the query.  The abridged version is not enough from which to determine a WTF.

     But, all in all, I say there may not be any WTF here at all.  There is not enough information provided to tell. 

     



  • @JeffS said:

    Why would I want a single answer shared among multiple questions? (Oooooo, I know... like an answer "No" so if we ever want to change all our "No"s to "Yes"s, it's just one database update!)
     

    Not all questions can be answered with a simple straight forward yes or no.  Many questions, however, may have the same answer, such as "try rebooting your computer" or "go f*** yourself"

     


Log in to reply