Database Design Related

Question

I would like the users be able to create their accounts (which stores the login and password information - Accounts table) and once the email is verified, they have the option of creating their own person record (which stores the other information such as first name, ... - People table). I also want the users (Account holders) be able to create person records other than themself. So when creating their own person record, users have the option of accepting a person record that was created by someone else instead of creating on their own or even be able to download a profile from other social networking sites and create the person record.

Each of these account holders also have several messages from other users and other social networking contents like blogs, ... My question is whether to associate these messages, blogs, employments,... to the account_id or person_id?

================================================================================= UPDATE: Alright, I guess I was not very creative in crafting my question. In a simple app, I could create just on USERS table and store all the login info as well as the user info such as first_name, ... and associate the messages, and other stuff with the user_id.

But as I want the users be able to create many Person records (not only for themself as well as others, which may include info about the users that have not yet created their accounts) I am thinking of separating the login info into ACCOUNTS table (or call it USERS table) and profile info into PEOPLE table. Each account holder will have only one Person record (their own and this is the info that identifies them to the world, as login info is just for system authentication only). Lets say YOU have created an account as well as your person record, and saw person Bill Gates and wanted to send message to him, we have two scenarios: (1) Bill Gates person record is not claimed by any account holder as their own person
(2) Bill Gates person record has an associated account holder In scenario (1) the system won't even let you send a message as there is no account holder that could check the messages and in scenario (2) should I save the BillGates person_id in the receivers_id of the MESSAGES table or BillGates.account.id ? Same applies to all the other content types, like blogs, photos, ...

Lets see, if I can put together the table relationships:

ACCOUNTS
id
login
password
person_id (account holders personal record)

---

PEOPLE
id
first_name
....
creater_id (current_account().person.id or just the current_account().id ?)
updater_id (current_account().person.id or just the current_account().id ?)

---

MESSAGES
sender_id (current_account().person.id or just the current_account().id ?)
receiver_id (person.id or person.account.id ?)
subject
body

---

BLOGS
id
title
body
author_id (current_account().person.id or just the current_account().id ?)

I hope I made my point. If not let me know, I will try to think of some other examples.

Answer 1

You already say it yourself. "Each of these account holders also have..."

Account holders have messages, blogs, etcetera. So these should have accountid's. If you want persons without accounts to have those you should use person_id's

Answer 2

I would say it depends on a purpose. If you want to specify ownership of a message, blog post, etc, I would use account_id as the person should be authenticated to create such a record.

If you want to tag that a post references other people, then I would use people_id

Answer 3

If there is always exactly one person associated with one account_holder, then they in a sense describe the same thing, and thus it doesn't matter where you put the blog, messages, etc. In this case, I would go with what ever is the most intuitive since it doesn't matter structurally, which would probably mean to put it in the Person table.

On the other hand, if you're suggesting more than one person can be associated with an account, or that you want people to somehow be able to have blogs, messages, etc. even without an associated account, then obviously those things have to be housed in the Person table.

Edit: It's very important to put an individual unique constraint on the foreign key (no matter which table you end up putting it in). Otherwise, your one-to-one association can easily become a one-to-many by accident if a careless user enters, for instance, a new account_holder row and gives it the same "ownerID" as a previous account_holder. In this case, the ownerID is a foreign key from the Person table and needs the unique constraint.

Edit #2: Looking over your question clarification, I see no reason not to have a single "USERS" table. Simply have a boolean "acitivated" attribute that means this person has an active account with valid login credentials. When a person creates an account but hasn't entered in any personal information, "activated" will be true but the personal information fields will remain NULL. And if that user creates other people's profiles, new rows will be inserted in USERS containing all their personal information but the "username" and "password" fields will be NULL and "activated" will be false. Wouldn't this be the most seamless and simple? User and account_holder are the same thing (which is why you were getting a one-to-one association), so why not leave it all in one table?