Mysql – Table Design – User Management

database-designMySQL

I have designed a database design for a simple User Management System, this system consist of the following things,

  1. User – A user may be a Vendor / Employee / Buyer.
  2. User – A user may have more than one Address – /delivery/ Billing / Profile / Company Address.
  3. User – A user can have more than one role- Sales Executive/Manager/CEO.
  4. User – A user can access all his roles / needs by using a single login credential (Username / Email address).

keeping in this, I have designed this system. This is my first application, I need to know whether the designed application will meet all requirements and is this good to go in this design?

Any suggestions and ideas regarding this and some tips for best practise. Thanks in advance.

enter image description here

Best Answer

The "Data Model Resource Book", Volume 1, has complete and validated structures for all that. I suggest you grab a coffee - it also discusses these details in great length.

You have a problem - User and UserRole - a User may be in multiple roles and roles should be time limited so they can change over time without invalidating your audit trail. What you do if a Vendor turns into a Buyer? You fall into the typical trap of assuming a User (entity / table) contains a role - it should not. A user is an ID - much like you passport does not contain your job.

Roles are attached to users (UserRole) table, but a user does not contain a role.

Same with AddressType - what if I want to use the same address for both invoicing and shipping? Types should be tags added. Or in the Role you have a pointer to a specific address (ShippingAddressId etc.).

You code the address very detailed - that is nice, but also totally bad if you ever run into addresses that are non-standard. There is a good sense to have fields like postal code, city, country - but even state is something irrelevant (never give it on an address) for shipping but what is worse are all the address fields. Street? What about countries not using this? Not having proper house numbers? Where your shipping address is 15 lines long? Those DO exist. Read http://online.wsj.com/news/articles/SB10001424052702304870304577489094121477570 for a reference. There is a good point to have a free text field for stuff like that - as well as additional routing details you are not aware of at the moment.

Phone numbers- same thing. Please. ContactItem is a good table - can be email, phone, etc.- Allows people to have multiple phone numbers.

PinCode? You want to store a readable pin code in there? Programming for beginners: NEVER EVER STORE A PASSWORD OR PIN CODE IN A DATABASE. Store a Hash, salted, by standard security approach. Something a hacker can not easily use to pretend to be the user. Storing a pin code was legally gross neglect 20 years ago. It still is today.

So, looks like a non-enterprise model made by someone now aware of the complexities of the real world. I can really only recommend a copy of the Data Model Resource Book for a good description of all those cases.

Related Question