August 24, 2010 at 8:44 am
On my current project we need to be store what we are calling citizenship documents (SSN, Passport, Driver's License) for people. Obviously this part is easy, even considering encryption. Here's the basic idea on how the system works.
In order to be included in the system a person must have a relationship with a unit OR a be the child of a person who has a relationship with a Unit. Depending on the Unit, country and the Relationship Type a person may be required to provide specific citizenship documentation. To keep it simple let's assume a relationship type of employee in the United States (since I'm there). In order to be entered into the system as an employee in the United States a person must provide either a Passport or a government issued picture id AND SSN to prove the person can legally be employed in the U.S. (it is much more complex than that even in the U.S.). So the question is how do I create table that will return the requirements based on Unit, Country, Relationship Type? Basic Table would be something like:
CREATE TABLE dbo.possibly_required_documents
(
country_code CHAR(2) Not Null,
unit_id INT Not Null,
relationship_type_id INT Not Null,
document_type_id INT Not Null
)
Obviously not what I'll call the table. This issue becomes how do I note that a document type is required and if it requires a supporting document? An is_required flag doesn't work, but I want to keep it as simple as possible. Any ideas?
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 25, 2010 at 2:13 pm
Update on this especially since I haven't gotten any help yet. Essentially trying to model the requirements on the U.S. I9 form (link is to a pdf).
Any and all suggestions welcome and will be considered.
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 25, 2010 at 2:32 pm
Aren't you describing a constraint?
Before you can enter a record into the main table, some other table(s) may need to have the records that are part of this transaction?
Greg E
August 25, 2010 at 2:42 pm
Is the table design the documents associated with a person and are you then trying to determine if the requirements are met through a SELECT statement?
OR
Is the table design a reference table from which you could run a report generating the list of acceptable documents as presented on page 5 of your document?
August 25, 2010 at 3:51 pm
Jack, I'm not 100% sure what you are trying to do, but this might get you started. You are describing alternative sets of documents to satisfy the requirement. Run the code below, and it may get you thinking along the right lines. I changed the required document column to a case column, then created a table which describes all the alternatives for that case. In your example, alternative 1 is simply a passport, and alternative 2 is to have BOTH a Social Security Number and a government issued picture ID.
If the user has documents to satisfy either requirement, they are good to go.
Obviously this could be more normalized by creating a separate documents table and using document IDs, etc. (I say this because Paul is still out there, somewhere.) Again, this is just to try to get you started. Good luck.
declare @possibly_required_documents table
(
country_code CHAR(2) Not Null,
unit_id INT Not Null,
relationship_type_id INT Not Null,
required_documents_case INT Not Null
)
insert into @possibly_required_documents
select 'US', 234, 567, 1
declare @required_documents_cases table
(
required_documents_case INT Not Null,
alternative INT Not Null,
document varchar(30)
)
-- Case 1: either a Passport or a government issued picture id AND SSN
insert into @required_documents_cases
select 1,1,'Passport' union all
select 1,2,'SSN' union all
select 1,2,'Government Issued Picture'
selectp.country_code,
p.unit_id,
p.relationship_type_id,
p.required_documents_case,
c.alternative,
isnull(c.document,'None') as required_documents
from @possibly_required_documents p
left join @required_documents_cases c on c.required_documents_case = p.required_documents_case
__________________________________________________
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 25, 2010 at 4:00 pm
More of a logical/business requirement than a traditional database constraint unless check constraints can perform complex checking way beyond > 100. There seems to be some branching logic going on.
Perhaps a trigger that will update your table based on your logic, not that I can even begin to articulate the logic which it sounds like you need.
August 25, 2010 at 8:13 pm
Okay, thanks for comments, questions, and code. It's not really a constraint. This table or tables possibly, is needed so we can enforce business rules. Basically a person can become a member of our organization and when becoming a member we require that they meet the rules as outlined in the I9 in the U.S. We want to be able to do this dynamically and not hard-code the documents and combinations needed in the application, especially since we have at least 7 different countries where the rules may be different. So the flow in the application is, a person is entered and then is made a member, when you click the make member button the application verifies that the identification documents needed have been provided. So the psuedo code would be:
If person has citizenship document in document list A for country A OR person has citizenship doc in list B AND a citizenship doc in list C then allow membership else throw error.
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 26, 2010 at 4:18 am
Okay, you don't want to hard code the documents, but in a way, you'll have to. What you need to do is have a table that associates countries with required documents. Since required IDs seldom change, this shouldn't be too difficult to maintain.
Create Table CountryDocs (country_code char(2), document_type_id int);
--I'm guessing at the length of a country abbreviation
Create Table Documents (document_type_id int Identity (1,1), DocumentName varchar(30),
DocumentDescription varchar(500), ListType char(1));
--Identity field assumes doc doesn't have it's own code for a natural key
--List type is for the priority. The I-9 has 3 lists, for example.
-- In this case, I'd put Passport & Drivers License in ListType A since it can be one or the other.
-- SSN & Birth Certificate would be ListType B (or 2)
Create Table Country (country_code char(2), CountryName varchar(100));
CREATE TABLE dbo.possibly_required_documents --Your table
(
country_code CHAR(2) Not Null,
unit_id INT Not Null,
relationship_type_id INT Not Null,
document_type_id INT Not Null
);
Pseudo-code would be (think CASE statement or CTE): When Customer resident of Country A, get >=2 documents from CountryDocs where CountryCode = CountryA. When Customer resident of Country B, get 1 document from CountryDocs where CountryCode = CountryB... etc.
Your requirements for docs sound like business rules, so you have to decide if those business rules reside in SQL Server or in your application code. Regardless, once you have the documents linked to the appropriate country / countries, you just need to know the minimum required # of documents and how many from each ListType needs to be gotten from each country.
August 26, 2010 at 6:42 am
Perhaps something like this:
Member (table)
MemberID PK
=========
I9Rules (table)
I9RulesID PK
CountryCode PK FK
RuleDesc
=========
DocumentType (table)
DocumentTypeID PK
IsRequired bit not null
I9RuleID (FK)
=========
Document (table)
DocumentID PK
MemberID (FK)
DocumentTypeID (FK)
I modified the I9Rules table to make CountryCode party of the PK.
August 26, 2010 at 7:06 am
Bob,
Really liking your idea. I'm assuming the alternative column means I need each document with that alternative. I think this is a little more complex than what I was thinking, but I also think it works better than what I was thinking.
Brandie,
Your option has merits as well and your comment that is sounds like business rules is absolutely correct. The whole reason for this is to be able to have a data-driven (flexible) method to enforce this type of business rule. The system I'm working on has to work across many countries (I'm thinking like 10) that each will have different requirements because of governmental differences and organizational differences. I'm not sure the product owner (yes we are doing Scrum and I like it) even really understands what they have asked for. Makes it a bit harder to design and calls for flexibiliyt because odds are it will change.
I'll post what we do and a more complete schema, probably tomorrow. Need this done today, which means I'll probably be back later with problems:-P
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 26, 2010 at 8:17 am
Jack,
If I'm following this correctly, the issue is that you have different constraint relationships among the different supportiing documents: you have an OR group (need Doc A OR Doc B), and an AND group (must have one from the OR Group AND Doc C).
I've handled something similar in this way
CREATE TABLE dbo.RequiredDocuments
(
Document_type_id INT Not Null
ConstraintType_tx varchar(5)
)
ConstraintType_tx should be [AND/OR].
The idea is that a user is required to have any Document with a ConstraintType_tx = "AND", and at least one with ConstraintType_tx = "OR".
The key code comes when you try to determine whether someone "passes"; I do this with a series of Exists statements.
I'm assuming that there are two tables in your Schema; one PotentialMember, and another, MemberDocumentation, that has a FK to the RequiredDocument table.
To test that they pass the AND requirement, use a NOT (NOT EXISTS) (double negative makes a positive, right?); this should prove that the PotentialMember is not missing any RequiredDocumnets that have an AND requirement.
Testing that they match at least one from the OR group is a simple EXISTS.
I'll admit that I haven't tested this code for this schema, and I'm adapting it from a much more convoluted system. I have to handle a NOT group (e.g. the Member should NOT have a certain attribute); multiple OR groups, in other words, one from each (A or B) OR (C or D), but not (A or C), etc.; some of the elements in my system have no rules so I need to account for that; and I have to use nested NOT EXISTS, instead of the simpler NOT NOT EXISTS). Basically, I'm confessing that this may not work for you out of the box, but I believe the concept is sound.
SELECT
*
FROM
dbo.PotentialMember pm
INNER JOIN dbo.MemberDocumentation md ON pm.Member_id = md.Member_id
WHERE
NOT
(
NOT EXISTS
(
SELECT
*
FROM
dbo.RequiredDocument rd1
WHERE
rd1.ConstraintType_tx = 'AND'
AND rd1.Document_Type_id = md.Document_Type_id
)
)
AND
EXISTS
(
SELECT
*
FROM
dbo.RequiredDocument rd1
WHERE
rd1.ConstraintType_tx = 'OR'
AND rd1.Document_Type_id = md.Document_Type_id
)
I hope this helps.
August 26, 2010 at 3:34 pm
Okay, I'm back and that means more questions.
Basically using Bob's solution. My 2 validation tables are: Citizenship_Document_Requirements and Citizenship_Document_Requirement_Rules
I've attached a script that has the needed tables and limited sample data.
My only issue now is that I don't have a way to enforce RI (other than a trigger) between the 2 tables above because Citizenship_Document_Requirement_Rule_No can't be a PK or unique index and I really love DRI and hate using triggers for this and I hate orphaned rows even more... I also can't use the surrogate key for the relationship because we are really joining on
The self-referencing AND column is because you need say a document_type from Rule_Group 1 OR a document_type from Rule_Group 2 AND one from Rule_Group 3.
I'd appreciate comments. I'm thinking I'll return a validation object containing the necessary data and let the validation pass or fail in the business layer.
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 5:39 am
Jack,
I'll look at the script as soon as I get some free time, but real quick... I know this is a taboo thing to say amongst DBAs, but you may be stuck enforcing RI in the application layer instead of the database layer.
@Duck. Don't hurt me!!
August 27, 2010 at 7:06 am
Brandie Tarvin (8/27/2010)
Jack,I'll look at the script as soon as I get some free time, but real quick... I know this is a taboo thing to say amongst DBAs, but you may be stuck enforcing RI in the application layer instead of the database layer.
@Duck. Don't hurt me!!
Thanks. Hmm, RI enforced by the application. Yeah, that could be done. I thought I had a way to do an FK, but then I realized it it only almost works. I'd have to do a 3 table design so I could use rules for multiple Unit\Relationships. I could de-normalize to a single table and that eliminates all those issues. Stick with what I've got for now unless someone comes up with something better.
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 9:36 am
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)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply