Page 1 of 3 123 LastLast
Results 1 to 15 of 31

Thread: The FAN trap

  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question The FAN trap

    This is my first post, so be gentle.

    I am a fledgling database designer. ACTUALLY, I was supposed to be a data-entry clerk, but since they know what I'm majoring in in college...my job duties are kind of expanding... unfortunately, they're expanding a little faster than my knowledge is.
    "
    anyway, my back-story aside, I'm working on the data model for a database that is supposed to track the renewal dates for our independent contractors' credentials. Sounded easy enough at first, but as I started digging into the problem and re-examining my 'use cases' (I'm not sure i can even bear to call them use cases...) I've come across a problem.

    Problem: I can not figure out a way to model the relationships between the contractor class, the credential class, and the expiration dates so that I don't fall into the so-called "fan-trap".

    They keep insisting they need the specific date the credential expires, and they say they want to be able to pull reports on which credential expire within the month, within a user-specified date range, and which are expired.

    I'm not asking for anyone to like...do all the work, but if anyone has some suggestions on how to solve this problem, I'd be...really really appreciative. I haven't even taken my introductory database design class, so I've just been fumbling through my pile of books trying to find a solution, but so far, nothin doing.

    Thank you everyone in advance.

    I'm beginning to wonder if I should just make the expiration date an attribute of the credential class... or make the credential an attribute of the contractor class... >.< My brain hurts.

    ~atsuko
    Last edited by atsukoarai86; 09-02-08 at 15:24.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by atsukoarai86
    Problem: I can not figure out a way to model the relationships between the contractor class, the credential class, and the expiration dates so that I don't fall into the so-called "fan-trap".
    please describe the tables as you think they should look, and how a table relates to a class

    dates are most definitely an attribute of one table or the other

    also, please tell in your own words what a fan trap is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    If your data is in at least Fifth Normal Form then by implication you eliminate any fan trap. So the solution is to ensure that you decompose any non-key join dependencies appropriately.

    We can't easily show you how to do that because we aren't in a position to analyse your data for you. If you have identified the keys and dependencies then there are formal methods or common sense ones you can apply. If you need help with those fundamentals then I suggest a decent data modeling book such as Terry Halpin's, "Information Modeling and Relational Databases".

  4. #4
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by r937
    please describe the tables as you think they should look, and how a table relates to a class

    dates are most definitely an attribute of one table or the other

    also, please tell in your own words what a fan trap is

    Okay... let's see here... I don't know exactly how to answer some of these questions.

    1... I guess the way the table looks depends on how the class diagram looks... I think a table is the representation of object of a particular class, or the representation of relationships between objects of different classes.

    2. A fan trap is a class with two relationships with a Many cardinality on the outside ends, causing false or lost information from a route.


    So far, I see my class Contractor having the attributes Name, and ID Number, with relationships to the JobPosition class, which has a position code and a position name...and a Location class, which has the Cost Center Number, Site Symbol, and the Site Name... the relationships between the Contractor class and the Position and Location classes wasn't too difficult...

    It's how to keep track of the expiration dates of the credentials I'm stumped on. I have a list of more than a few credentials to associate to one contractor, so initially I decided that Credential needs to be a class with Credential Name and Credential ID (like State Licensure with an ID STA), and the relationship between the Contractor Class and Credential Class would be 1..n going that way and 0..n from Credential to Contractor.

    When I try to throw in the date is where I run into problems with cardinality. In one solution, I made the Expiration Date an attribute of the Credential class, and made the Cred-Contractor relationship 0..1 and the Contractor-Cred relationship 1..n... and I thought yay problem solved

    Then when I considered what the data-entry form might look like I thought... "So, this is going to force the data person to manually type in each credential over and over and over each time a new contractor is added to the system... which is almost certainly and probably very quickly going to lead to inconsistencies in the data..."

    And I couldn't picture how the table for Credential would look if it's attributes were Cred_ID, Cred_Name and ExpDate, since the on object can't even have an expiration date if it doesn't have a relationship to a particular provider.

    So I'm stuck with inconsistent, redundant information as my solution.

    i hope this helps... ;-; I'm at my wit's end.

  5. #5
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Red face

    Quote Originally Posted by dportas
    If your data is in at least Fifth Normal Form then by implication you eliminate any fan trap. So the solution is to ensure that you decompose any non-key join dependencies appropriately.
    ;-; I don't know what a single word of that means.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by atsukoarai86
    ;-; I don't know what a single word of that means.
    don't feel bad, nobody else does either

    sounds like you want 3 tables, one for the contractors, one for the credentials, and one -- called a relationship or intersection or junction table -- for the data regarding a particular contractor's credentials

    this 3rd table is where the date goes

    p.s. i don't know what a "class" is -- in data modelling, there is no such thing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by atsukoarai86
    ;-; I don't know what a single word of that means.
    If you don't know what Fifth Normal Form is then it would be wise to take a course or study some books before doing database design.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you know what Third Normal Form is, you'll be fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Lightbulb

    Quote Originally Posted by r937
    don't feel bad, nobody else does either

    sounds like you want 3 tables, one for the contractors, one for the credentials, and one -- called a relationship or intersection or junction table -- for the data regarding a particular contractor's credentials

    this 3rd table is where the date goes

    p.s. i don't know what a "class" is -- in data modelling, there is no such thing

    As far as the "class" thing goes, that's just what I call an entity, because that's what the author of this...very... vague and uninformative text-book calls them...and it's kind of a carry-over for me from OOD.

    So anyway, what you're saying is, I have one table... that has the contractor information, one with the credential information, and one table that... demonstrates the relationship between the contractors and credentials and tosses the expiration date in there too...

    so the expiration date isn't an entity at all, it's an attribute of my contractor/credential/exp.Date table? I'm trying to picture what the ER diagram for that would look like, but I'm drawing a blank. I still end up with a bunch of lines connecting contractors on one side and dates on the other to the credential in the middle... which still looks like a fan to me.

    I made a little diagram of the problem I'm having. It's embarrassingly bad, I'm sure but... I'm a total n00b.
    Attached Thumbnails Attached Thumbnails roughExample.jpg  

  10. #10
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by dportas
    If you don't know what Fifth Normal Form is then it would be wise to take a course or study some books before doing database design.
    well, you can just tell that to my boss. I'm taking my first class this semester. this is kind of a trial by fire thing.

    In any event, what you're saying doesn't help with the problem I'm having.

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by r937
    if you know what Third Normal Form is, you'll be fine
    Occassionally you will hear such things: that 3NF is "good enough for most people". I have never seen much evidence to support it.

    Some fairly common data modeling problems are not solved by 3NF. It's possible that some people will simply not notice those problems or not mind them but I fail to see how it's better not to know about them. Knowing and applying 5NF is the way to know (and solve) those problems.

    5NF is much more important than 3NF. 3NF as properly defined is arguably a mistake - a hangover from the early days of the relational model, which still exists mainly for historical reasons. BCNF is its later and better refinement and many people still confuse the two.

  12. #12
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by dportas
    Occassionally you will hear such things: that 3NF is "good enough for most people". I have never seen much evidence to support it.

    Some fairly common data modeling problems are not solved by 3NF. It's possible that some people will simply not notice those problems or not mind them but I fail to see how it's better not to know about them. Knowing and applying 5NF is the way to know (and solve) those problems.

    5NF is much more important than 3NF. 3NF as properly defined is arguably a mistake - a hangover from the early days of the relational model, which still exists mainly for historical reasons. BCNF is its later and better refinement and many people still confuse the two.

    : \ >_< Okay that's it. I'm gonna have to go look up what...all those things are.

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    atsukoarai86. :-) Do that. Actually I'm impressed, though surprised at first, that you know about connection traps but not 5NF. Relational Modeling courses tend to treat them as part of the same topic but the term originates with ER models so it's quite reasonable to know one without knowing the other.

  14. #14
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Wink

    Quote Originally Posted by dportas
    atsukoarai86. :-) Do that. Actually I'm impressed, though surprised at first, that you know about connection traps but not 5NF. Relational Modeling courses tend to treat them as part of the same topic but the term originates with ER models so it's quite reasonable to know one without knowing the other.

    awww... ^^; that makes me feel a little better and less n00bish. all i can say is I'm very eager to get this semester on the move so I can LERN. er. LEARN.

    /cheer.

    i'll ask my teacher tomorrow. That'll be a great opener. "Hi, nice to meet you. What's fifth normal form...?" Oh yeah. that is EXACTLY what I'm gonna say.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by atsukoarai86
    I'm trying to picture what the ER diagram for that would look like, but I'm drawing a blank.
    it would look like the attached

    the data would look like this

    Contractors
    21 Todd 7⅜
    24 Fred 8
    27 Biff 7

    Credentials
    101 MCSE C-
    103 Ph.D. B
    107 OBE A+

    ContractorCredentials
    21 101 2008-12-31
    21 107 NULL
    27 103 2010-01-01


    Todd has an MCSE which expires this year, as well as an OBE which never expires

    Fred has no credentials

    Biff has a Ph.D. which expires in 15 months


    simple, eh?

    Attached Thumbnails Attached Thumbnails atsukoarai86.gif  
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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