Database design question (conditional mandatory fields)

Question

Hi,
Just looking for some opinions/ideas on how best to do this.

I'm building an extranet system where users are added by the administrators manually (there's no ability for someone to "register" themselves). When they're added only a small amount of mandatory data is entered (name, email, password), they are then emailed their login, and when they first access the system they are asked for additional mandatory details that must be completed before being given access to the full site.

I can think of three ways to define this in the DB:

1. Set all mandatory fields to NOT NULL in the DB, and enter empty strings for all the "conditionally mandatory" fields when creating the user (I don't like this, seems hacky)

2. Set only the initial mandatory fields to NOT NULL in the DB, and enter NULL for all the "conditionally mandatory" fields when creating the user (I don't like this, while technically correct, the schema wouldn't reflect the real world user entity, where all the data is mandatory)

3. Have two tables, one for new users, and one for complete users (at first glance this seems to be the best option, however other tables need to have relationships that point to the user table, and this would complicate that/make it impossible)

Any thoughts?
Cheers,
Jack

Edit

So looks like number 2 would be the best approach, with a field that defines the status of the record (new/complete) and either check constraints or a trigger to check whether or not all the "optionally mandatory" fields have a value. I'm guessing check constraints would be the most suitable option, but I'll give both a go and see which works best. Thanks.

Answer 1

I'd go for the pragmatic approach of one table, with a status column to indicate whether the user is 'privisional' or 'confirmed' (say), and check constraints to ensure that all the mandatory information is present when the status is 'confirmed'.

Whether you should use nulls or empty strings is something I don't have much of an opinion on, as I am used to Oracle, which can't distinguish between the two!

Answer 2

How about if you try number two but add a field to show the status as intialized, fully registered. Then the fields could be null but when the value changes to fully registerd, a trigger could ensure all the required fields are filled out.

Answer 3

I would go with option 3. This is assuming that the system does not do anything with "new users" except wait for them to convert to "complete users."

Have a newusers table containing the minimum amount of required fields. Set up any relationships to other tables when the user converts to a complete user, not when the new user is created. This will get rid of the need to establish relationships to both the newusers and users tables (you'll only need them for the users table).

Answer 4

A little hacky but you could have one table for new users and one for registered users. Once a user registers they are removed from the new users table and all data is in the registered users table. To get access to the full site one must be in the registered users table. The new user table is only used to allow access to the registration page.