Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

The "Data Model Ressource Book", VOlume 1, has complete and validated structures for all that. I suggest you ggrab a coffe - it also discusses these details in great lengths.

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 Venduor turns into a Buyer? You fall into the typcal 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 wnt 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 ahve 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 isa good point to hava fre text field for stuff like that - as well as additional routing details you are not aware of at the moment.

Phone numbrs- 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 approeach. 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 Moedel Ressource Book for a good description of all those cases.

share|improve this answer
    
thanks for your recommendation, this is my first application, I am learning now. –  rathishDBA Oct 29 '13 at 9:46

Your Answer

 
discard

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.