A new requirement has surfaced on an old codebase, that basically enables direct (internal) communication between two formerly not-directly related classes of users (stored in different tables with completely different schema and, sadly, the code is barely OO-aware, much less designed, so there’s no parent class). Since we’re out to hang a bag on this old setup that never considered this functionality, there’s no guarantee that there are no PK collisions — given the dataset in use, it’s practically guaranteed that there ARE.
So, the solution seems obvious: kill it with fire and rewrite the whole mess A mapping table. I’ve gotten two directions for the possible ways to implement the map, but I’m not a DBA, so I’m uncertain if there are any pros and cons I’ve missed.
For the sake of clarifying the abstraction, consider three groups of disparate user data: Professors, Administration, Students (No, this isn’t a homework assignment. Promise!)
Mapping 1
(professor_id, admin_id, and student_id are foreign keys to their respective tables)
| mailing_id (KEY) | professor_id | admin_id | student_id |
-------------------------------------------------------
| 1001 | NULL | 87 | NULL |
| 1002 | 123 | NULL | NULL |
| 1003 | NULL | NULL | 123 |
The +/- to this approach seem pretty heavy on the cons:
- Two “wasted” fields per row
- Violates 2NF
- Vulnerable to insert/update anomalies (a row with only 0-1 field set NULL, e.g.)
The pros are not without their own merits, though:
- The mapping can be accomplished with a single lookup
- Easily determine the “source” data for a given user from the mailing_id
Truth be told, in my gut, I don’t like this idea at all.
Mapping 2
(assume MSG_* are defined constants, enum types, or another suitable identifier)
| mailing_id (KEY) | user_type (UNIQUE1) | internal_id (UNIQUE2)|
------------------------------------------------------------------
| 1001 | MSG_ADMIN | 87 |
| 1002 | MSG_PROF | 123 |
| 1003 | MSG_STUDENT | 123 |
With this setup, and a unique composite index of {user_type, internal_id} things become much cleaner, 3NF is maintained, and the application code doesn’t have to check for I/U anomalies.
On the downside, there’s a bit of a loss of transparency in determining the user source tables that has to be handled outside of the DB, basically amounting to an application-level mapping of user_type values to tables. Right now, I’m (rather strongly) leaning toward this 2nd mapping, since the downside is rather minor.
BUT I’m painfully aware of my own limitations, and am sure I’ve probably missed advantages or stumbling blocks in both directions, so I turn to wiser minds than mine.
5
Your second idea is the correct one. This approach lets you do all of the mapping that you need to do in order to integrate your three colliding key spaces.
Importantly, it lets the database impose most of the consistency that you need to have using declarative constraints.
You already have more code than you want to have, so don’t add more code than absolutely necessary to keep your integrated key list consistent. Let your database engine do what it was built to do.
The “problem child” that is giving you discomfort in Mapping 2 is the USER_TYPE column. This column is important because you need it to ensure that INTERNAL_ID only appears at most once per user type. The only time you need any code that is even aware of USER_TYPE is the code that inserts and deletes from your mapping table. This can be localized pretty well. I would assume that you’ll create a single point in your code where the mapping table content is maintained. An extra column in this one spot where data is written is not a big deal. What you really want to avoid is adding the extra column everywhere the data is read.
The code in your sub-applications that needs to use the mapping can be blissfully ignorant of the USER_TYPE simply by giving each sub-application a view that filters the mappings down to the one application-specific user type.
From experience my recommendation is to choose consistency over elegance or ‘best practice’. That is to match the existing design and go with THREE mailing tables (one for each role) with a simple mailing_id, user_id field structure.
It’s inelegant but it has a few advantages…
- Matching the existing structure will be easier for anyone
else who will work on this schema before it’s put out to pasture. - You have no wasted fields and you’re not asking the db to match up things
that won’t exist. - Because each table will only to one other and it’ll be relatively easy to make
a view which ties up all the data for your routines to use.
I’m sure many others will disagree with this approach, but primary aims of normalisation and best practices are to make code more consistent so it’s easier to follow and debug… and obviously bringing the whole codebase up to scratch is probably not feasible.
3