Normalize users table to reference email addresses in another table?
jonathanbromley opened this issue · 3 comments
Happy to receive any suggestions including "please don't do that because...".
I want to use PHP-Auth in an application that needs authentication, but will make other uses of email addresses (for example, newsletter subscribers who are not "users" and don't have a login). Thanks to its clean code I think I can see how to modify PHP-Auth so that the 'email' column in the 'users' table is instead a foreign key into another table, not managed by PHP-Auth, that contains email addresses. But I REALLY want to avoid modifying PHP-Auth if possible, for obvious reasons.
Another option might be to keep all my application's email addresses in the PHP-Auth 'users' table, with some of the 'users' not being PHP-Auth users at all. Other parts of the application would get to the email addresses in the 'users' table via a foreign key. What's the cleanest/safest way to mark a row in the 'users' table as "not a user account at all, just a row that holds an email address"?
EDIT: looking again, it's clear that I can do this simply by ensuring that the password hash column is empty. Maybe this is the best solution?
And finally I could keep all my application's emails in another table, and accept that email addresses in PHP-Auth's 'users' table are duplicate data. Just suck up the denormalization.
I can't believe I'm the only person to have encountered this design issue, so I'd appreciate any pointers for how it can be handled cleanly. Sincere apologies if this is something for which an obvious solution already exists...
Thanks for your question!
This has indeed been the first time this has been asked here, as far as I can remember.
There are multiple ways to do what you want, as you have already explored, and there is no right or wrong but they mostly depend on your application logic and what you’re actually trying to accomplish. Of course, they can also affect maintainability.
Not modifying the code of this library here comes with the advantage that you will still be able to do seamless updates.
If your other uses of email addresses (e.g. newsletters) are functionally separate, it’s not obvious why you would want to tie the two together. There is no rule that says you must not have two database tables that may contain email addresses.
When Auth#register returns, for example, you could also insert the email used to register into your newsletter subscribers table.
You proposed managing your newsletter subscribers in the users table of this library. Well, in that case, in addition to setting users.password to an empty string, you could perhaps set both users.verified and users.resettable to 0 as well. But the implications for your application logic are more important: Do you really want this? Will a newsletter subscriber be able to sign up for a regular account then? How will you mark regular users that unsubscribe from your newsletter?
To me, all this naturally sounds like two database tables for two different purposes. With the addition, perhaps, of updating the newsletter subscriber’s email address when Auth#confirmEmail returns and the old email is in the list of subscribers.
What do you think?
Thank you for the speedy, detailed and helpful response!
I agree that I could use two completely separate tables, but there would be a great deal of duplication/overlap and that just feels nasty (I'm no database theorist but it seems to me that everyone hates the idea of duplicate data, preferring normalized tables with foreign-key references).
Assuming I pursue the idea of entries in the users table that are merely email addresses, I'd expect any other usage of the emails to be done using a relations table with foreign keys pointing into users, so there's no problem with unsubscribing or any other operations not directly related to PHP-Auth. I agree, though, that it would quite badly muddle the application logic for such things as a subscriber eventually deciding to sign up for a full account.
Ultimately, the implications of abusing fields in the users table may be too much. In particular, a future update of PHP-Auth could change the fields in ways that might break my assumptions. So I might just pinch my nose and have two separate tables with a lot of overlap.
Once again, many thanks (and thanks, too, for PHP-Auth being such a clean and usable thing).
It just seems that you want to force two things into one table and column that are naturally two separate things that should go with two tables. You notice the solution starts to look dirty in some ways and some requirements for the application logic require workarounds or are not entirely clear.
In the end, it’s up to you what solution you want to go with and what better fits your requirements and intended usage, but a change to the database schema generally comes with major versions only, so there’s not as much danger there as you might think.