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

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

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".

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.

;-; 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

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.

if you know what Third Normal Form is, you'll be fine

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.

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.

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.

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.

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?