stuck on database design for user management app

Question

I'm trying to write up a user management app, but getting confused about how to structure the database properly.

I think the database design would be structured something like this:

Users

• user_id
• first_name
• last_name
• email

Accounts

• account_id
• ldap_access
• drupal_access
• billing_app_access
• facebook_access

Notes

• note_id
• description
• date_added

And I believe the mapping would go like this:

One user can have one set of accounts (1:1)

One set of accounts can have many users (1:m)

One set of accounts can have many notes (1:m)

Many Notes can have Many Sets of Accounts (m:m)

Is this the correct way to structure the database? I'm really confused since I would like the applications fields be a boolean true/false for that particular user. (If they have an account on there or not), and the notes would correspond to EACH of the fields within the Accounts database. (ie: note_id:1 would correspond to user johndoe, application: facebook, note_description = "user no longer has facebook")

Does this make sense?

Any help would be greatly appreciated!

Answer 1

I prefer this design (used in many system like Windows or Oracle)

Table User User-Role Role Access Application Note (still wonder what this for)

Role useful if you have many user with similar characteristic.

Answer 2

Mayby something like this:

I still don't understand all your needs.