Tough inheritance database/model design decision

Question

I have Users which can be either TypeS, TypeC, or TypeA. I have models for each of these types to store additional information. Now, in the Users table, should I have

1. 3 nullable foreign-key fields to designate which type they are
2. 2 fields, 1 with the name of the type and 1 with the foreign key
3. 1 field designating the type with the foreign key on the other model
4. no field on the user, and rely on checking the reverse relationship?

I'm using Django if you want to provide a more refined answer.

Answer 1

I've encountered this situation a couple of times myself. My personal preference is option 1 (unless there are going to be 20 types of users).

• Option 1 gives you the option to make foreign key references therefor guaranteeing database integrity (with a constraint on the three columns).

• Option 2 this isn't an actual foreign key reference. The record could no longer exist in the Type-table. Table joins are out of the question.

• Option 3 even worse than option 2, the value needs to be interpreted before navigation to the Type-table.

• Option 4 possible, but it's a bit like hunting for your type-data. It would allow one user to have multiple type definitions.

Answer 2

I would use option #1, 3 nullable foreign keys. It allows you to use actual database foreign key relationships, which options #2, #3, and #4 wouldn't. Because of that you'll get:

• the easiest and fastest lookups for the extra info
• The wasted disk space is minimal
• The programming logic for determining which type it is is not complex (though slightly more complex than it would be if you had a the "two-field" possibility.)

As far as part 2 of your question, I think I'd have one nullable foreign key to another table to hold the business-specific fields.

EDIT: There is one reason to consider option #2... if you expect that it's possible that the number of types might grow from 3 to 10 or 100, option #1 system will become increasingly annoying..

Answer 3

Django provides Generic relations as part of the contenttypes framework, which allows you to implement something similar to your option #2, just more flexible. You can establish generic relations by adding the following fields to your model:

content_type = models.ForeignKey(ContentType)
object_id = models.PositiveIntegerField()
content_object = generic.GenericForeignKey('content_type', 'object_id')

This way you can assign one of TypeS, TypeC, or TypeA to each user in question. Also, should you ever have the need to add a TypeX or a TypeY, you're already set and don't need to extend the model.