Is it better to have two separate user tables or one?

Question

My web app is going to have two types of users that are 100% different - in fields, functions, and purpose on the site. The purpose of one type of user is to post blogs, and the purpose of the other is to read them and "follow" the posters.

The ONLY stuff they have in common are the need for an id, email, password and a couple other bits of meta data such as date joined, etc.

Should I attempt to stem them from the same account table, or make one table for each?

NOTES

-One potential thing to think about is that, to prevent user confusion, I think emails between the two types of accounts should be unique.

-Readers and Authors will have different sets of data that will need to be stored about them aside from the normal "account" meta data.

Fasano

If they won't intersect in anyway in the future (one type will be able to become another), I think it's fine to go with two tables. – Louralourdes

They wont intersect, but what about enforcing unique emails between them - aka, if I am an author, I shouldn't be able to sign in as a reader with the same email ... right? – Fasano

It sounds like your primary concern is still consistency(?). Using a single table is more appropriate for that, and also more in line with the relational model. And the differentiation between user types really just amounts to a flag, right? – Whodunit

Mario, I suppose so - I guess an account StARTS at the same place (with email, password, user id) no matter what – Fasano

Answer 1

I would separate them.

TABLE User {
    id
    email
    password
    salt
    created
    modified
}

TABLE follower {
    user_id
    ...
}

TABLE author {
    user_id
    ...
}

Over time your application will grow. Perhaps now you have two destinct roles - but it may change in the future. You may have authors that also follow other authors, or followers that are "upgraded" to authors. You might also start adding other "roles" and want something like this:

TABLE user_role {
    user_id
    role_id
}

TABLE role {
    id
    name
}

Scald

Hmm, the roles concept is an interesting way to look at the problem. Is it common to link "roles" with different types of info stored, as well? – Fasano

Well, the roles table is generally just a "handle" so you can setup multiple groups that users can be in for your ACL. For example, you might have admins in the "user", "author", and "admin" roles while authors are only in the "author" & "moderator" roles. – Scald

Xeon, awesome I think I get it. If you have a second, I've asked another question based on your answer, here: #7540949 – Fasano

Answer 2

it will save you allot of work to just have one table and have a single column in the table that defines which type they are .

Zeb

Answer 3

Define "user". If a user is somebody who has registered him or herself on your site, using an e-mail address, password and nickname, and who can log on, then stick everyone in the users table.

The things users can or can not do on a site does not differ for a user. It's their permissions that are different. Map permissions to users in a separate table. Don't create a table for each type of user.

Otherwise, when you're adding a new kind of permission in the future, you don't have to add a new table for that type of user (and alter all (sql) code that handles with users: logging in, resetting passwords, and so on). You just add a new type of permission, and map that to their respective users.

Klump

That's a good point...might be good to point out, though, that authors and users might potentially have full sets of different data to be stored about their account. – Fasano

That would perfectly fit in the author_profiles and users_profiles table, for example. – Klump

Answer 4

No, make one table for each. It will be easier to manage, it'll be scalable

Pickle

To clarify, you mean one table PER type of user, ya? The question I just ran into after thinking about it is - what about enforcing unique email addresses between them? – Fasano

I would NOT create two tables. This will make things MUCH harder to manage. You need just one user table - with multiple relation tables for additional types. – Scald