Which unique id to use for blogs and comments?

Question

This is a question that arose from the consequences of another question here: Is it better to have two separate user tables or one?

Assuming I have two types of users, an Author and a Reader, each stored in relational tables keyed to a main Accounts table like so:

TABLE Accounts {
    id
    email
    password
    salt
    created
    modified
}

TABLE reader {
    id
    user_id
    ...
}

TABLE author {
    id
    user_id
    ...
}

When an Author posts a blog, should I tag the blog with the unique id from the Authors table, or the unique id from the Accounts table? Same goes with reader comments - should I tag the comment with the Reader table unique id, or the Account table unique id?

So, basically either:

TABLE Blogs {
    id
    author_id
}

OR

TABLE Blogs {
    id
    account_id
}

Which is less likely to bite back later on?

Browse

Accounts. Why would the Reader or Author table have a distinct id anyway? – Nathanson

Well, I might be very wrong, but from my understanding, it's good to key a table with a standard auto-incrementing key. Again, I'm no pro. – Browse

You could just have Reader and Author id column be a foreign-key to Accounts so that which id to use is irrelevant. – Vintner

That's the general rule I suppose. But since the keys are already unique in the origin table, they will be by transition if you split them up into two tables. Thusly I would not introduce a separate ordering (doesn't help indexing afaik). – Nathanson

Answer 1

TABLE user {
    id
    email
    password
    salt
    created
    modified
}

TABLE profile {
    user_id
    age
    favorite_movie
    ... other useless stuff...
}

TABLE user_role {
    user_id
    role_id
}

TABLE role {
    id
    name (author, admin, user, subscriber, etc...)
}

TABLE blog {
    id
    user_id
    title
    text
    ...etc...
}


user HASMANY role
user HASONE profile
user HASONE blog

So a user can be an admin, and an author. You can find their blogs by looking for a matching blog for this user_id. If you have account type dependant fields then place them all in the "profile" table.

Hemiplegia

Answer 2

Only you can answer fully, but my gut says that the blog entries should be tagged by author. The only reason to use account conceptually would be if a non-author can create (author) a blog post. So far, with the info provided, this does not look to be the case.

Note that I also think that all authors should be users: everybody is a user, but only some users also have authorship status.

Archive

Thanks for the answer...actually authors and users in this case have a very different set of permissions, which mostly do not overlap. It's definitely a strange situation. Users and follow authors, read blogs. Authors can't follow anybody but they can write blogs. – Browse

they can read yes...its more of a horizontal relationship to users vs. a vertical though. If that makes sense... – Browse

I understand it but it still doesn't make sense: if an author wants to consume content, then they have to create a separate user account. But whatever, your site not mine. – Archive