August 27, 2010 at 10:21 am
Jack,
In looking at the I-9 form from your previous link (2nd post in thread), I see something that might help you out:
You need to produce documentation that
1. Establishes your identity, and
2. Establishes employment authorization.
Some documents do both, some don't. However, you need to have both conditions satisfied.
So, focusing on meeting both conditions, here's what I see:
CREATE TABLE Documents (
DocumentID INT NOT NULL IDENTITY() PRIMARY KEY CLUSTERED,
Name varchar(100) NOT NULL UNIQUE,
EstablishesIdentity bit NOT NULL DEFAULT (0),
EstablishesWorkAuthorization bit NOT NULL DEFAULT (0));
-- might need fields for the country that these are valid for, etc.
--table for Person (w/ PersonID), and PersonDocuments:
CREATE TABLE Persons (
PersonID INT NOT NULL IDENTITY() PRIMARY KEY CLUSTERED,
Name, --etc
IsMember bit NOT NULL DEFAULT 0);
CREATE TABLE PersonDocuments (
PersonID INT, -- FK back to Persons table
DocumentID INT, -- FK back to Documents table
CONSTRAINT PK_PersonDocuments PRIMARY KEY CLUSTERED ON (PersonID, DocumentID));
--And a function that returns whether if the Person has all of the
--necessary documents that are needed to become a member:
CREATE FUNCTION EligibleToBeAMember(@PersonID int)
WITH SCHEMABINDING
RETURNS TABLE
AS
SELECT p.PersonID,
EstablishesIdentity = max(d.EstablishesIdentity),
EstablishesWorkAuthorization = max(d.EstablishesWorkAuthorization)
FROM Persons p
JOIN PersonDocuments pd
ON p.PersonID = pd.PersonID
JOIN Documents d
ON d.DocumentID = pd.DocumentID
WHERE p.PersonID = @PersonID
GROUP BY p.PersonID;
What you're looking for is a 1 (true) being returned by the function in the two fields.
Of course, this will need some work to handle different country requirements, but I think it will handle the requirements of the I-9 for here in the US.
How does this look to you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 11:41 am
Wow, Wayne, that's a majorly cool solution. I like.
The only concern I'd have is scalability, but that's dependent on what the other countries' docs "look like."
EDIT: Then again, I'm not sure it answers Jack's FK question.
Jack, What does Citizenship_Document_Requirement_Rule_No mean to you? I ask because it seems awfully similar to the ID field.
August 27, 2010 at 12:15 pm
The Dixie Flatline (8/27/2010)
Hey Jack, I have a thought, but first I need to know the maximum documents would you ever expect to be joined in an "AND" condition? For example:Alternative 1: Passport (1)
Alternative 2: Social Security Number AND Drivers License AND Library Card (3)
Alternative 3: Social Security Number AND Drivers License AND Hall Pass (3)
Alternative 4: Library Card AND a Hall Pass (2)
I don't have the answer. For the U.S. it is only 1 AND. For other countries there may be different requirements (still under investigation) so begin able to do multiple AND's and multiple OR's is what needs to happen. Current design handles that okay.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2010 at 12:26 pm
WayneS (8/27/2010)
Jack,In looking at the I-9 form from your previous link (2nd post in thread), I see something that might help you out:
You need to produce documentation that
1. Establishes your identity, and
2. Establishes employment authorization.
Some documents do both, some don't. However, you need to have both conditions satisfied.
So, focusing on meeting both conditions, here's what I see:
CREATE TABLE Documents (DocumentID INT NOT NULL IDENTITY() PRIMARY KEY CLUSTERED,
Name varchar(100) NOT NULL UNIQUE,
EstablishesIdentity bit NOT NULL DEFAULT (0),
EstablishesWorkAuthorization bit NOT NULL DEFAULT (0));
-- might need fields for the country that these are valid for, etc.
--table for Person (w/ PersonID), and PersonDocuments:
CREATE TABLE Persons (
PersonID INT NOT NULL IDENTITY() PRIMARY KEY CLUSTERED,
Name, --etc
IsMember bit NOT NULL DEFAULT 0);
CREATE TABLE PersonDocuments (
PersonID INT, -- FK back to Persons table
DocumentID INT, -- FK back to Documents table
CONSTRAINT PK_PersonDocuments PRIMARY KEY CLUSTERED ON (PersonID, DocumentID));
--And a function that returns whether if the Person has all of the
--necessary documents that are needed to become a member:
CREATE FUNCTION EligibleToBeAMember(@PersonID int)
WITH SCHEMABINDING
RETURNS TABLE
AS
SELECT p.PersonID,
EstablishesIdentity = max(d.EstablishesIdentity),
EstablishesWorkAuthorization = max(d.EstablishesWorkAuthorization)
FROM Persons p
JOIN PersonDocuments pd
ON p.PersonID = pd.PersonID
JOIN Documents d
ON d.DocumentID = pd.DocumentID
WHERE p.PersonID = @PersonID
GROUP BY p.PersonID;
What you're looking for is a 1 (true) being returned by the function in the two fields.
Of course, this will need some work to handle different country requirements, but I think it will handle the requirements of the I-9 for here in the US.
How does this look to you?
Wayne,
Thanks. This is a very nice solution, but as you said at the end, it definitely meets US requirements, but I can't say that it will for other countries. as is. It may, but I just don't know, while the current solution I think... will work.
One other issue is that while for some relationships in the organization both are required, for others only 1 may be required. This is another reason I like the modified implementation of Bob's solution I posted in the attachment because I can define 1 or more required documents with OR'ing and AND'ing. If I was just doing US then I'd probably go with this implementation.
Simple, elegant, and easy to understand.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2010 at 1:34 pm
I'll suggest this modification instead
Pick an arbitrary maximum number, say 5, even 10. Conceptually, there are alternative SETS of documents that satisfy your conditions. So let's define document SETS containing from 1 - 10 documents and have your rules refer to alternative document SETS. For simplicity's sake, I'm thinking of a table like this, or you could normalize it.
Your test is to see if any alternate set is satisfied by the documents you have associated with a give person. There is no need for your "AND" column. All the documents in a given set are ANDed together. ORs are only between sets.
For example:
Person in question has the following documents: SSN, Library Card, Get Out of Jail Free Card
Requirements are
1. Passport
or 2. SSN and Driver's License
or 3. SSN and Library Card and Ace of Diamonds
or 4. SSN and Library Card and Get Out of Jail Free Card
What I originally called alternatives (numbers 1-4) are in fact sets of 1-X documents. Notice that SSN gets listed in more than one set. In the above example, you would actually compare each of the alternative sets to the set of actual documents. If any one set can be satisfied you're in business.
For simplicity's sake you could set up a DOC_SET table like this:
DOC_SET_ID INT PrimaryKey
,DOC1 INT
,DOC2 INT
,DOC3 INT
,DOC4 INT
,DOC5 INT
,DOC6 INT
,DOC7 INT
,DOC8 INT
,DOC9 INT
,DOC10 INT
Or you could normalize it and use multiple rows to define the set.
DOC_SET_ID INT
,DOC INT
The up front cost is defining all the sets explicitly rather than trying to write rules to handle the and/ors. However, I think you will find this approach easier to deal with in the long run.
Sorry for the sloppy formatting, but I'm doing this on my break. I know you have the ability to code without needing to cut and paste. If this isn't sufficient I'll come back to it this weekend.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 27, 2010 at 1:42 pm
Brandie Tarvin (8/27/2010)
Wow, Wayne, that's a majorly cool solution. I like.The only concern I'd have is scalability, but that's dependent on what the other countries' docs "look like."
EDIT: Then again, I'm not sure it answers Jack's FK question.
Jack, What does Citizenship_Document_Requirement_Rule_No mean to you? I ask because it seems awfully similar to the ID field.
Brandie,
The ID is a surrogate key that really isn't necessary except that we are using Linq to SQL and L2S loves surrogate keys. Also the Rule_No will work with Rule_Group so you'll have rule 1 with groups 1, 2, and 3. So the natural key would be:
Citizenship_Document_Type_ID
Citizenship_Document_Requirement_Rule_No,
Rule_Group
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2010 at 3:47 pm
Jack Corbett (8/27/2010)
Wayne,Thanks. This is a very nice solution, but as you said at the end, it definitely meets US requirements, but I can't say that it will for other countries. as is. It may, but I just don't know, while the current solution I think... will work.
One other issue is that while for some relationships in the organization both are required, for others only 1 may be required. This is another reason I like the modified implementation of Bob's solution I posted in the attachment because I can define 1 or more required documents with OR'ing and AND'ing. If I was just doing US then I'd probably go with this implementation.
Simple, elegant, and easy to understand.
Drats! I was hoping this would handle it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 27, 2010 at 9:09 pm
WayneS (8/27/2010)
Jack Corbett (8/27/2010)
Wayne,Thanks. This is a very nice solution, but as you said at the end, it definitely meets US requirements, but I can't say that it will for other countries. as is. It may, but I just don't know, while the current solution I think... will work.
One other issue is that while for some relationships in the organization both are required, for others only 1 may be required. This is another reason I like the modified implementation of Bob's solution I posted in the attachment because I can define 1 or more required documents with OR'ing and AND'ing. If I was just doing US then I'd probably go with this implementation.
Simple, elegant, and easy to understand.
Drats! I was hoping this would handle it.
It's possible it would, I just don't know at this point. Regardless it is still a good design.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2010 at 10:11 pm
I've used a combination of Wayne and Bob's ideas in a similar type of application (credentialling various types of clinical staff, to make sure they held all of the licenses to practice in a hospital in vraius settings. They actually dovetail nicely as well, except that Wayne's is more scalable for maintenance aspect, while bob's is more scalable for the day-today checking piece.
So you end up defining:
- "atomic" requirements: In the case below "residency status" and "employability status" (and the "identification" aspect, since at least one of them needs to positively identify you).
- requirement "sets": each set of requirements that will allow you to "pass" (whatever the test happens to be).
- the "point of reference": in this case likely used to handle any potential differences between countries and their documents. In our case it was what specialization they could practice, etc...
- sets of alternates: documents that can be substitutes for each other for at least one of the requirements. Documents can be part of more than one set.
- a mapping of the sets to the atomic requirements.
Once you have this highly normalized like this, it's fairly easy to maintain the document sets. That said, it was actually easier to then produce a denormalized set (with just the set number and the specific document) to use for the checking. Unless your list gets ludicrous, recreating the "checking table" takes a few second if you need to add/remove documents.
We actually push if one level further, by adding a sixth level (all valid "patterns" for the licenses presented) so that we could validate that the license type didn't get picked incorrectly. The DV handles all of the RI needed, and the validation app has been running for the last 12 years with no issues. We have literally thousand of possible combos you could use, so this can scale out pretty well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 27, 2010 at 10:22 pm
Jack Corbett (8/27/2010)
WayneS (8/27/2010)
Jack Corbett (8/27/2010)
Wayne,Thanks. This is a very nice solution, but as you said at the end, it definitely meets US requirements, but I can't say that it will for other countries. as is. It may, but I just don't know, while the current solution I think... will work.
One other issue is that while for some relationships in the organization both are required, for others only 1 may be required. This is another reason I like the modified implementation of Bob's solution I posted in the attachment because I can define 1 or more required documents with OR'ing and AND'ing. If I was just doing US then I'd probably go with this implementation.
Simple, elegant, and easy to understand.
Drats! I was hoping this would handle it.
It's possible it would, I just don't know at this point. Regardless it is still a good design.
Thanks.
I think the key was in recognizing that you weren't necessary looking for certain documents, but documents that verified certain requirements. By going to that thought-process, it became an easy thing to implement by flagging which requirement(s) each document fulfilled. Hopefully, once you get all the requirements for the other countries and whatever other conditions that you've alluded to, you would still be able to use this. If you needed multiple for a certain condition, you could just sum the field (after converting to tinyint) to see if you had the quantity required. (If you actually change the column's datatype to tinyint, be sure to add a check constraint to restrict it to just 0/1).
I'll be watching this thread to see how it progresses... and to see if anything else comes to mind.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply