locked
Database design Suggestion RRS feed

  • Question

  • Consider this company hierarchy.

    Company ---- > Customer Groups ----- > Customers

    Company ---- > employee

    Company ----- > Office Employees

    Company -------> Contractors

     

      Customer groups – Each groups has customers assigned to it.
      Employees – who work on field at customer site.
      Office Employees- Who work in head office to manage above customer and employees, their payroll and billing.
      Contractors are those who provide additional employees to company if required.

    My Query is ................... i need to design a database where i can store login details for the all the above users. Each of the type customer, employee, office employees, contractors, need to login in the web based system, to view their respective work.

    I have database already in use, which works in intranet network, where only office user can use the software. there is one table for each of them, customer, employee, office users and contractors. but now it needs to go on internet also, where company customers, employee and contractors can use the software using there own logins. and how would the login web page look like ? smart solution suggestion required.

    U can send me diagram @ berawala.krunal@gmail.com

    Thanks.

    Wednesday, November 10, 2010 5:00 AM

Answers

  • This is a very broad question and hard to answer without a lot more information. It sounds like you are looking to build a web based application and centralise your database tier. There is a lot of information out there but without a lot more info. it hard to say what you need, your web page layout will depend on how the application is used by your users, that is the first question I would be asking, you need to come up with the user requirements. Have a look at the following link regarding building a web based application...

    http://msdn.microsoft.com/en-us/library/ee658099.aspx

    ...note there are many issues to consider especially security if this application is to be accessible over the internet.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    Wednesday, November 10, 2010 6:09 AM
  • Hi Krunal, I am not sure how your current database design is but as per my understanding you can take a look on below ERD which can be suitable for your requirement... (IF)

    Case 1: If any employee/Office employee/Contractors not a customer then,

    UserLogin
    ----------------
    LoginID (PK)
    Password
    LoginTypeID (FK)
    CreatedDate
    ModifiedDate

    LoginType
    -------------------
    LoginTypeID (PK)
    LoginType (EMP/OEMP/CON/CUST)
    CreatedDate
    ModifiedDate

    Case 2: If any employee/Office employee/Contractors also become a customer then,

    UserLogin
    ---------------
    LoginID (PK)
    Password
    CreatedDate
    ModifiedDate

    LoginType
    ----------------
    LoginTypeID (PK)
    LoginType (EMP/OEMP/CON/CUST)
    CreatedDate
    ModifiedDate

    UserLoginDetails
    ------------------------
    RefLoginID (FK)
    RefLoginTypeID (FK)
    CreatedDate
    ModifiedDate

    For User's information details, you can add one more table as UserDetails and point to LoginID to store more details for employee/Office employee/Contractors/Customer.

    -->and how would the login web page look like? Smart solution suggestion required.

    It depends on you how you like, I mean if they are ok with select “Login Type” in login Page along with User and Password input otherwise you can validate the “Login Type” information inside application code then redirect web pages accordingly also fine...

    You need to think and discuss more on Web Page Layout with your client on this....

    Hope this helps.

    thanks,
    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Wednesday, November 10, 2010 6:30 AM

All replies

  • This is a very broad question and hard to answer without a lot more information. It sounds like you are looking to build a web based application and centralise your database tier. There is a lot of information out there but without a lot more info. it hard to say what you need, your web page layout will depend on how the application is used by your users, that is the first question I would be asking, you need to come up with the user requirements. Have a look at the following link regarding building a web based application...

    http://msdn.microsoft.com/en-us/library/ee658099.aspx

    ...note there are many issues to consider especially security if this application is to be accessible over the internet.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    Wednesday, November 10, 2010 6:09 AM
  • Hi Krunal, I am not sure how your current database design is but as per my understanding you can take a look on below ERD which can be suitable for your requirement... (IF)

    Case 1: If any employee/Office employee/Contractors not a customer then,

    UserLogin
    ----------------
    LoginID (PK)
    Password
    LoginTypeID (FK)
    CreatedDate
    ModifiedDate

    LoginType
    -------------------
    LoginTypeID (PK)
    LoginType (EMP/OEMP/CON/CUST)
    CreatedDate
    ModifiedDate

    Case 2: If any employee/Office employee/Contractors also become a customer then,

    UserLogin
    ---------------
    LoginID (PK)
    Password
    CreatedDate
    ModifiedDate

    LoginType
    ----------------
    LoginTypeID (PK)
    LoginType (EMP/OEMP/CON/CUST)
    CreatedDate
    ModifiedDate

    UserLoginDetails
    ------------------------
    RefLoginID (FK)
    RefLoginTypeID (FK)
    CreatedDate
    ModifiedDate

    For User's information details, you can add one more table as UserDetails and point to LoginID to store more details for employee/Office employee/Contractors/Customer.

    -->and how would the login web page look like? Smart solution suggestion required.

    It depends on you how you like, I mean if they are ok with select “Login Type” in login Page along with User and Password input otherwise you can validate the “Login Type” information inside application code then redirect web pages accordingly also fine...

    You need to think and discuss more on Web Page Layout with your client on this....

    Hope this helps.

    thanks,
    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Wednesday, November 10, 2010 6:30 AM