Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
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

1 Answer 1

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.

share|improve this answer
    
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 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 at 0:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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