Is there a way to make multiple columns unique in Access 2007?

Question

I am currently trying to make a database for the place I work. We put partial social security numbers into the system to keep the patrons separate so we know who is who and I am trying to figure out if there is a way to make that easier. We are looking for a way to keep people from using the each other's social security cards [something that does happen] so while I understand how to make a single column unique, I was wondering if there is a way to make it so that all of the SSN columns would be unique, as there would be multiple.

Main SSN Spouse's SSN Children's SSNs

Sometimes there are up to seven or eight children and they all have to go in a separate column. So is there a way to make all of those SSN columns unique from each other? And if not in Access, is there a program that does it? We are currently using MyDatabase but I haven't found a way to make anything unique in there.

Answer 1

You are going at this the wrong way. What if you get a family with 19 children? You will need 21 SSN columns. Do you really want to have to modify all your forms and reports when a larger family than your design allows for shows up?

The uniqueness problem:

1. You can't simply define a unique multi-column index on all of your SSN columns and call it done. Consider the sequences: [A], [A,B], [A,B,C]. These would all be unique in your index, but clearly A and B are being duplicated, contrary to your wishes.
2. OK, so what if you try to solve the 8 SSN column uniqueness problem using a query with joins? Just to verify that the Main SSN is unique, you will have to join the patron table 7 times: MainSSN -> SpouseSSN -> Child1SSN -> ... -> Child8SSN. The Cartesian product on a 1000 row table joined 7 times would be 1.0 * 10^21 row comparisons. Indexing all 8 columns might speed this up a little, but at the expense of greatly increased storage, and vastly slower inserts and updates.
3. And, after all that effort, you still haven't determined if the Spouse SSN is not duplicated among the Child SSNs! For completeness, you must then check Child1 vs. Children2-8, Child2 vs. Children3-8, etc.

In short, it is impractical to enforce uniqueness among 8 columns, let alone a reasonable maximum, like 21.

Aside: it is also probably a bad idea to store even partial Social Security Numbers in an unencrypted format. It makes uniqueness checks more likely to fail, and is probably against regulations.

My proposed fix:

This is a data design problem. What I suggest is instead of going horizontal (adding more columns), you go vertical (add related tables with more rows). For your main table, I suggest:

Table: Patrons
==============
Field       | Type        | Comment
------------------------------------------
PatronID    | AutoNumber  | Primary Key
PatronSSN   | String      | SSN Digest

And then, your spouses table:

Table: PatronSpouses
====================
Field           | Type        | Comment
------------------------------------------------------
PatronSpouseID  | AutoNumber  | Primary Key
PatronID        | Long        | Foreign Key to Patrons
SpouseSSN       | String      | SSN Digest

Finally, your children table:

Table: PatronChildren
=====================
Field          | Type        | Comment
------------------------------------------------------
PatronChildID  | AutoNumber  | Primary Key
PatronID       | Long        | Foreign Key to Patrons
ChildSSN       | String      | SSN Digest

Now, define indexes:

Table.Field             | Index Type
-----------------------------------------
Patrons.PatronSSN         (unique)
PatronSpouses.SpouseSSN   (unique)
PatronChildren.ChildSSN   (unique)

Next, you define table relationships:

Foreign Table.Field     ==> Primary Table.Field   Relationship
--------------------------------------------------------------
PatronSpouses.PatronID  ==> Patrons.PatronID      (1 to 1)
PatronChildren.PatronID ==> Patrons.PatronID      (1 to Many)

Advantages:

1. You get automatic uniqueness checking on all of your SSNs in each table.
2. You can easily cross-check SSNs between the tables by joining on their respective SSN fields.
3. The uniqueness check will never get more complicated than 1. and 2.
4. Enforcement of 1. automatically happens at the table level. Enforcement of 2 still happens at the code/query level.
5. Your forms and reports can now handle any number of children without further modification. True, the initial design becomes more complex, but you will never be forced to change it when an exceptionally large family walks in.