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!

  • 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
  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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