10

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.

  • 2
    You might find Martin Fowler's ideas on roles an interesting read. – Marjan Venema Dec 31 '13 at 10:46
  • It was, indeed, interesting. Sadly not too much insight into my specific problem – GeminiDomino Dec 31 '13 at 16:26
  • You're going to get professors who become administrators and students who get jobs in the administration or even come back 10 years later as faculty. You probably already have them. Are you going to keep those separate or try to unify? – Elin Dec 31 '13 at 19:09
  • The roles are just examples, but I see your point. In practice, even if users did switch roles, they would remain as separate records anyway. – GeminiDomino Dec 31 '13 at 19:11
  • If would be great if you'd re-phrase the first paragraph. It's a bit unclear. I mean, it's obvious there's a problem but it's not clear enough what it is. – Tulains Córdova Aug 5 '16 at 19:30
1

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.

3

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

  1. Matching the existing structure will be easier for anyone else who will work on this schema before it's put out to pasture.
  2. You have no wasted fields and you're not asking the db to match up things that won't exist.
  3. 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.

  • The problem with that approach is that the database cannot then enforce uniqueness in mailing ids, which is the primary purpose of the mapping in the first place: otherwise, pairing the individual ID fields from each table, with a "user type" indicator could be done without any change. – GeminiDomino Dec 31 '13 at 20:54
  • I do see what you're getting at but having worked on that kind of system I've given an option that you might not have considered. So far as I see it the mailing id would need some content to refer to somewhere (what was mailed or how to find the document) so mailing id should be a foreign key anyway which means uniqueness issues would be resolved elsewhere. As I read it, the admin student and prof data tables being linked to may have different structures so I can't see the user type field adding value. The original developers must have hit this issue, what did they do? – James Snell Jan 1 '14 at 22:13
  • The "user type" field would determine which table to associate with that particular record. It would have to be handled on the application level either way, and since they ARE in different tables, there's no good way to make it a foreign key constraint. The original developers don't seem to have considered this problem at all, unfortunately, which is why it's turning into such a mess. :) – GeminiDomino Jan 2 '14 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.