4

The users I am concerned with can either be "unconfirmed" or "confirmed". The latter means they get full access, where the former means they are pending on approval from a moderator. I am unsure how to design the database to account for this structure.

One thought I had was to have 2 different tables: confirmedUser and unconfirmedUser that are pretty similar except that unconfirmedUser has extra fields (such as "emailConfirmed" or "confirmationCode"). This is slightly impractical as I have to copy over all the info when a user does get accepted (although I imagine it won't be that bad - not expecting heavy traffic).

The second way I imagined this would be to actually put all the users in the same table and have a key towards a table with the extra "unconfirmed" data if need be (perhaps also add a "confirmed" flag in the user table).

What are the advantages adn disadvantages of each approach and is there perhaps a better way to design the database?

Henry Henrinson
  • 5,203
  • 7
  • 44
  • 76

4 Answers4

5

The first approach means you'll need to write every query you have for two tables - for everything that's common. Bad (tm). The second option is definitely better. That way you can add a simple where confirmed = True (or False) as required for specific access.

What you could actually ponder over is whether or not the confirmed data (not the user, just the data) is stored in the same table. Perhaps it would be cleaner + normalized to have all confirmation data in a separate table so you left join confirmation on confirmation.userid = users.id where users.id is not null (or similar, or inner join, or get all + filter in server side script, etc.) to get only confirmed users. The additional data like confirmation email, date, etc. can be stored here.

aneroid
  • 12,983
  • 3
  • 36
  • 66
  • 1
    If you keep the confirmation status and data in a separate table remember to make `confirmation.userid` a Unique (+ Foreign) Key or Primary (+ Foreign) in the `confirmation` table. – aneroid Aug 23 '12 at 16:52
3

Personally I would go for your second option: 1 users table with a confirmed/pending column of type boolean. Copying over data from one table to another identical table is impractical.

You can then create groups and attach specific access rights to each group and assign each user to a specific group if the need arises.

StephenMeyer
  • 196
  • 8
3

Logically, this is inheritance (aka. category, subclassing, subtype, generalization hierarchy etc.).

Physically, inheritance can be implemented in 3 ways, as mentioned here, here, here and probably in many other places on SO.

In this particular case, the strategy with all types in the same table seems most appropriate1, since the hierarchy is simple and unlikely to gain new subclasses, subclasses differ by only a few fields and you need to maintain the parent-level key (i.e. unconfirmed and confirmed user should not have overlapping keys).

1 I.e. the "second way" mentioned in your question. Whether to also put the confirmation data in the same table depends on the needed cardinality - i.e. is there a 1:N relationship there?

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

the Best way to do this is to have a Table for the users with a Status ID as a Foreign Key, the Status Table would have all the different types of Confirmations all the different combinations that you could have. this is the best way, in my opinion, to structure the Database for Normalization and for your programming needs.

so your Status Table would look like this

StatusID | Description
=============================================
1        | confirmed
2        | unconfirmed
3        | CC confirmed
4        | CC unconfirmed
5        | acct confirmed CC unconfirmed
6        | all confirmed

user table

userID | StatusID
=================
456    |    1
457    |    2
458    |    2
459    |    1

if you have a need for the Confirmation Code, you can store that inside the user table. and program it to change after it is used, so that you can use that same field if they need to reset a password or what ever.

maybe I am assuming too much?

Malachi
  • 3,205
  • 4
  • 29
  • 46
  • @aneroid are you talking about my approaches? – Malachi Aug 23 '12 at 16:34
  • @aneroid if you had the Email and Confirmation date in the user table it could be left Null and you could just do a query on that column where `IS NOT NULL` or `IS NULL` – Malachi Aug 23 '12 at 16:38
  • Yes, but only the 'normalized' aspect of it. You don't need the separate `confirmed/unconfirmed` status since in my soln, the presence of the user's id in the confirmation table indicates that the user is confirmed. The `Status` table you described provides no benefit since 'confirmed' can be stored as a boolean True/False or integer 0/1 in the same table. Doesn't need an english 'confirmed/unconfirmed' mapping. – aneroid Aug 23 '12 at 16:38
  • Also correct. But then where do you draw the line between when you want to move normalize-able data out? If later there is other related info, you could continue adding it in NULL and non-NULL column value checks and end up with a bad design. OTOH, one could also say that something as critical as 'confirmation status' should be stored in the same table. Hence I said, _"What you could actually ponder over is..."_ – aneroid Aug 23 '12 at 16:46
  • I see what you are saying, and agree. I was thinking that he would want to have a confirmed E-mail status, confirmed Credit card, etc depending on what he is using this for. with the status id table you could have the different combinations and still only have to query one table – Malachi Aug 23 '12 at 16:48
  • if it is just a simple Confirmation then you could do a confirmation Date. I see your Point. it depends on what he wants to store in the database really. – Malachi Aug 23 '12 at 16:50
  • Exactly. Btw, even if he's storing the confirmation info with other things like email, credit card, etc. then he could still have the confirmation data separate **and** a status (so he can have semi-confirmed users if he wants) -- but that status still wouldn't need to be a separate table as you described. Since he will already have decided which numbers to use. (Not sure why the `@malione` keeps getting deleted from my comments above). – aneroid Aug 23 '12 at 16:56