1

I have seen a few database designs where it has all user information in an account table, including password, email, DOB, First Name, Last Name etc.

I have seen some others that have two tables

username(or email), password,state(activated etc), group(admin, owner, user etc)

and

nameFirst, nameLast, birthDay,birthMonth,birthYear etc

What are the pro's and cons of the above methods?

Hailwood
  • 89,623
  • 107
  • 270
  • 423
  • 3
    Personally I prefer to separate user-related data and authentication-related data (table per authentication method). In this case you're able to maintain a lot of authentication systems easily. – zerkms Mar 30 '11 at 02:20
  • 2 tables, `user` and `user_info` it's my way – Koerr Mar 30 '11 at 02:29

4 Answers4

2

The difference between the two designs is mostly one of flexibility. If the account and user data share a single table, then each user must have an account, and each account can have only one user (unless you add another table to allow child users to be added in addition to the user that lives with the account data, or unless you add new records with duplicate account details in each one, which is very bad and antithetical to what databases are supposed to do).

With two tables, you can easily have multiple users in each account, and might also choose to allow circumstances where an account has no users, or where a user does not have an account, if doing so would benefit your use-case.

The tradeoff is that if you want to do something like determine the account for a user (or the user(s) in an account), you have to do a join if you are using two tables. If you have one table all you have to do is fetch the row to get this information.

aroth
  • 54,026
  • 20
  • 135
  • 176
1

Well, the obvious main problem is that you have to deal with two tables if you want information from both. That's likely to complicate your queries a little and possibly reduce performance.

Since all that information is dependent on a single key field (username most likely here), I tend to put it al in one table except in one very specific scenario: if, for example, you wanted to give someone access to the details in the first table but not the second, you could split it up for the purposes of security (opening up the first to everyone but restricting the second to only those who need the extra detail - but I'd probably move the password to the second table in that case).

Other than that, I'd minimise the number of objects as long as it didn't get in the way of maintaining third normal form.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • I think that authentication information is needed just once, when user is authenticated. – zerkms Mar 30 '11 at 02:21
  • @zerkms, that's fine, but you're not _required_ to get every column out when you query the DB. You simply ask for what you need, regardless of how much the table holds. – paxdiablo Mar 30 '11 at 02:23
  • 1
    I know that, I just pointed to your "Well, the obvious main problem is that you have to deal with two tables if you want information from both. ". In the point of view that authenticated table is used just once - this is not so often. And in other hand - user data table will be fewer, thus more likely it will fit in memory. – zerkms Mar 30 '11 at 02:25
0

I believe it is up to you the database designer. As long as it is not difficult for you to work with in the future via server side languages - it is a viable solution.

I have set up dbs with credentials and sensitive data (encrypted) in one table and all the other stuff in another table. I have also set up dbs that have a table to house all of that data.

Either way it takes only 1 query statement to get and/or manipulate the data from one or two tables.

Chris McClellan
  • 1,123
  • 7
  • 14
0

Keeping information in seperate tables if we are talking about the same 'object' (the user and her extra information belong together), is something i prefer to avoid. But I can think of two good reasons to split them up:

  • If you have designed, or are using, a seperate authentication system with its own table, e.g. User, but you need to add additional information. Or you have a standard system, but the information/fields for a user depends on your client: names of fields, amount of fields ... Then you can keep the authentication part standard, and the extra information part is known to be flexible.

  • If inside your data-model you have an elaborate Person/People model, with adresses, birthdates, what have you not, you could choose to use the same table to store that information for your users as well. So you user would then also have a person_id or something similar.

Hope this helps.

nathanvda
  • 49,707
  • 13
  • 117
  • 139