Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, namely:

  • Supporting new login mechanisms
  • Supporting new user types (ie new subscriptions plans)
  • Supporting new user roles

Here are a few screenshots of my unfinished schema. Do you see any problems / data inconsistencies that could arise from this design?

Orange squares are primary keys, and blue squares are foreign keys. Had to split this up into two images.

Users / Subscriptions / Auth Groups:

enter image description here

User Groups / Subscription Types:

enter image description here

Thanks in advance!

share|improve this question
dis you use a software for the diagram ? – Gabriel Solomon Feb 15 '11 at 11:38

I think you overdid normalization:

  • I dont see the purpose of the user_names table, i would store the names in the user table
  • Same for basic_user table, i would store the email and password in the user table as well
  • A subscription can have multiple statuses? if not i would put id_status in the subscription table
share|improve this answer
Some of the accounts will be created via API, and first and last names will not always be available. That is the reason for storing them separately. For basic users, a password will be needed, wheras with Facebook users Facebook handles the auth and we just check the Facebook id. Yep, a subscription can have multiple statuses. I do think I need to store email in the users table, however, sine all accounts WILL have an email associated with them (even Facebook). Thanks for your feedback! – Calvin Froedge Feb 16 '11 at 4:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.