Page 1 of 5 123 ... LastLast
Results 1 to 15 of 74
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    EAV vs a table per attribute

    Given a requirement for a database design that allows users to add attributes and a requirement that no NULL values are permitted in the physical schema, would it be best to use an EAV-style table and maintain views for each attribute where the tuples take the form (entity_id, attribute_value), or to create one table for each attribute (where the tuples also take the form (entity_id, attribute_value)? I tend to lean towards creating physical schema as these are true relational tables... but I'm wondering if there might be an advantage to using EAV & views..

    btw, I know that doing joins may produce NULL values; these queries would incorporate NOT NULL or replace the NULL values with a default.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by luapyeltrah
    ...and a requirement that no NULL values are permitted in the physical schema...
    ...since your specs were clearly written by idiots, in the long-term it probably matters not which design you employ.
    But if it were me, I would store that undefined data in an XML column, rather than implement EAV.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Moderators

    How come, on what basis, do some people get away with name calling ?
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Hi Derek,

    Do you really class the use of "idiot" in Blindmans post offensive? If Blindman was calling the poster or another contributor an idiot directly, then yes, I could see a problem. However, Blindman was using the term to punctuate the point of his post.

    If anyone actually takes offence to the use of "idiot" in Blindmans post please speak up.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I suppose someone might have cause to be offended. Others will just see Blindman's outburst as uninformed and fatuous.

    There are plenty of people and some whole organisations who successfully implement database designs without using nulls. They include some highly respected practitioners in their fields, some of them known to me. Blindman's outbust seems to imply that he thinks they are idiots. It's probably not worth trying to refute him though and I suspect Blindman isn't going to change his mind. Some people might think his moniker is well chosen however...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by dportas
    I suppose someone might have cause to be offended. Others will just see Blindman's outburst as uninformed and fatuous.
    Well yeah, the idiots anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    gvee

    Evidently, you are missing the point.

    1 Behaviour is behaviour, it is either acceptable or unacceptable, the target of the attack does not have to be online and complain personally for themselves.

    2 I found it offensive, which is why I posted. Since you need that identified explicitly: I find blindman's post offensive.

    3 What you are doing is explaining and condoning blindman's behaviour. Evidently the moderators work for blindman, without being asked. I find your justification of blindmans attack offensive as well.

    4 It is difficult to have a technical discussion about the subject (here re Nulls; elsewhere regarding other technical subjects) when the starting position is anyone using them is an "idiot". Even if we do start, we know where the thread will go: the technical matter gets drowned out by personal attacks and opinions without evidence.

    5 Whatever rules you people have (there are none posted) are not applied consistently: if I were to call anyone who allows Nulls an "idioit", I would get volumes of attacks, and the moderators would do nothing about it; they would say I started it, and back the blind mans attacks (that has happened in other threads, and I did not have to call people names, directly or indirectly, perceived implication was enough !).

    ----------

    6 Just in case you were unsure as to whether it was a direct attack on not, and completely proving you post false, you now have the blind man making a direct attack (post #6) on dportas (as opposed to indirect attack against anyone who supports "No Nulls"). Let's see what the moderators do.

    ----------

    Anyway, the attacks and name calling are simple evidence of:
    • immaturity (needing everyone to agree with them; attacking anyone who doesn't)
    • simplistic "rules"; instead of actual theoretical and practical knowledge and understanding
    • black-or-white thinking; inability to understand shades of grey, practical application, pragmatism
    • absence of actual technical knowledge and experience in the stated subject
    • and thus, no surprise, inability to discuss technical subjects

    Some posters here keep telling us they are "database" "experts", but when the slightest question is raised about any technical issue, they immediately switch to verbal abuse. This could be a good site; but the numbers are very low. There is a reason for that: a few obsessive self-proclaimed "database" "experts" who behave badly, and proffer no actual knowledge or experience re databases. (No doubt they have experience with linked spreadsheets poured into a container called "database", but that is a different subject, not "Database Concepts and Design".) That could change if there was tolerance of new posters who have knowledge and experience in the stated subject, but no, the obsessives attack anyone with knowledge and experience (actually anyone disagreeing with their simplistic "rules"), so they leave; and the site remains low tech, high emotion, low value.

    That's why I have asked before "does blindmen own this site ?". The usual response from the moderators is "oh well, they have helped many others before". That may be so, but it
    (a) abdicates the moderator responsibility of dealing with the current attack
    (b) does not constitute a justification for bad behaviour, and
    (c) avoids the fact that are giving advice on matters beyond their capability (10,000 one-liners answering minor technical questions does not imply that they can answer a major or intermediate question).

    As long as you people perform this biased form of moderation, as evidenced, and allow (condone, support, justify) this bad behaviour from the few, other posters are drowned out; they will not post against the established "database" "expertise" evidenced here. The site will remain low tech, high emotion, low value, owned by a few loud and vociferous individuals.
    Last edited by Derek Asirvadem; 09-28-09 at 21:02.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I don't speak up often about behavior on DBForums. I encourage (and nearly demand) a high degree of tolerance for opposing opinions. I do not intend to turn this into a debate but I am willing to address your points even though you won't like what I have to say.

    1) Correct. Behavior also needs to be interpreted in context.

    2) Interesting.

    3) No.

    4) You really need to attempt to explain what you mean by this point.

    5) You must have never looked for the rules or chosen to ignore them because they were inconvenient.

    6) No. If there was any attack, that attack was only on idiots. Based on his response, dportas didn't see this as an attack and shouldn't have.

    The rules as posted were created as a result of problems with people that twisted the posted rules to their own advantage at other sites. They would dodge and weave, trying to use our own rules to prevent the moderators from being able to deal with the mischief that they created. The rules here are simple: each moderator acts independantly as though they owned DBForums. The moderators each have an investment of time and passion in DBForums, and are expected to keep the discussion clean, civil, and productive but beyond that I place no obligations on them. They do an amazing job for no pay, and I really don't believe that you could buy a better staff at any price.

    I am personally something of a "closet academic" and follow the developments and details of database theory with relish. Unfortunately I have to live and work in "the real world" where business needs mercilessly trump academic interests in a fraction of a heartbeat. I do not spout theory here at DBForums except when it is directly relevant to a practical problem at hand. Theory might interest a few people, but only a few and it would provoke arguments of the type that ought to be reserved for religious dogma.

    Blindman has contributed to DBForums for at least as long as I've been here. You're right in that he can be acerbic and has very strong opinions, but his opinions are based on his experience and I've never seen him lose an argument on either logical or technical grounds. Blindman sometimes makes himself look bad because he won't relent, but I've only seen that happen when the other party tries to force a point that they can't support with either logic or technical content.

    I've already written three times as much text as I intended, and spent twice as much time writing it as I feel it is worth. I'll post this unedited, feel free to dissect it to your heart's content, I'm done!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Sep 2009
    Posts
    16
    My first post to this forum certainly started up something... In my own subjective view, I wasn't bothered by what Blindman said, even though when he said "idiots" he (unknowingly?) was referring to me as I was the one deciding on the design criteria. I would say it's because 1) we're on the internet, I don't know him, he doesn't know me, so who cares, and 2) referring me as an idiot was in regards to using NULL values, which had absolutely nothing to do with my question.

    just a couple points to what Pat said:

    Quote Originally Posted by Pat Phelan
    Blindman has contributed to DBForums for at least as long as I've been here. You're right in that he can be acerbic and has very strong opinions, but his opinions are based on his experience and I've never seen him lose an argument on either logical or technical grounds.
    Blindman didn't offer any substantive reason for why using NULLs makes me an idiot.

    Blindman sometimes makes himself look bad because he won't relent, but I've only seen that happen when the other party tries to force a point that they can't support with either logic or technical content.
    I wasn't forcing any point about NULL values, and my question had nothing to do with NULL values.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by luapyeltrah
    ...in regards to using NULL values, which had absolutely nothing to do with my question.
    au contraire, it was right there in your opening post --
    a requirement that no NULL values are permitted in the physical schema,
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    luapyeltrah

    Wouldn't it of been better to just tell us what the database needs to model rather than just setting yourself two seemingly random solutions and then asking us to pick between them while not telling us what the actual problem is.

    Mike

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Derek: Man-up and quit whining. Its undignified.
    Luapyeltrah: I was unaware that you were the source for that requirement, though it still appears foolish. I could design a database without the use of zeros to avoid that pesky "Div Zero" error. But the fact that it can be done does not mean it should be done. It is not my responsibility to explain why you should allow nulls. It is your responsibility to justify a business requirement that needlessly restricts your design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by blindman
    It is your responsibility to justify a business requirement that needlessly restricts your design.
    Nullability is not a business requirement. Whether or not to use nulls is a design choice to be made by the database designer, in just the same way that the designer chooses types, names and other characteristics of the data model.

    The justification frequently used for not using nulls is that since all information can be represented accurately without nulls they are superfluous and they cause contradictory results that don't accurately represent everyday reality. Furthermore they frequently add needless complexity to the overall solution, not least because developers are forced to write extra code to handle them.

    I expect you are familiar with those arguments even if you disagree with the conclusions that others have drawn. But the case for not using nulls is certainly a reasoned one, supported by plenty of evidence of success in the real world. Dismissing people who work that way as "idiots" is obviously no argument at all and contrbutes nothing to the thread.

    Your comparison to zeros is not a reasonable one because a zero is a value whereas a null is not.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by dportas
    Nullability is not a business requirement.
    Yes, it is. Very often.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by blindman
    Quote Originally Posted by dportas
    Nullability is not a business requirement.
    Yes, it is. Very often.
    Can you give an example of what you mean? Null is just a symbol used in databases that is put there by database developers. There are no nulls in reality and so they are no proper part of any business requirement. Or to put it another way, if a business owner or analyst stipulates that a null is required then he/she is trespassing on database design issues that have nothing to do with actual business requirements. Why do you think otherwise?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •