Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,860
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1

    Advice needed to build a company-user database schema

    Hi all,

    I need some help to decide a good database schema for creating a system having multiple company-user entries.

    Basically, a company act as a sub-admin, which can manage(add/edit/delete) users under that company. Thus of course, the company itself has a Username and a Password

    Apart from the above two I need to have the following essential details for the company
    1. Company_Id (a unique identification number, not sure from which table to take it right now)
    2. Company short name(actually the Username for the company will be this value)
    3. Company name
    4. Email
    5. Website
    6. Address
    7. Phone numbers
    8. Maximum Intake


    Similarly, a user will have
    1. User_Id (again, to be taken from somewhere)
    2. Company_Id (To relate a user with its company)
    3. First name
    4. Last name
    5. Job title/Area of expertise
    6. Email
    7. Website
    8. Address
    9. Phone numbers


    -----------------------
    Now my plan is to have one table named Login having
    1. Account_Id
    2. Account_Type_Id (to differentiate normal user and company)
    3. Username
    4. Password
    5. Email
    6. Website
    7. Address
    8. Phone numbers


    and then two separate tables named User and Company(having an Account_Id field in common, to relate with Login table) to store the remaining details of the above two types.

    1. Would it OK, or do I need to consider anything else?
    2. Do I need to have any auto_increment fields in the last two tables?


    Sorry for this lengthy post and thanks for reading
    Last edited by abduraooft; 03-28-2009 at 08:04 AM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    If you want my opinion, I don't think you should have a login for a company. Instead, carry different security levels for the users, and give some users admin access for an entire company.

    After all, the company cannot login and use your system; only people that own or work for a company can do that.

  • Users who have thanked Fumigator for this post:

    abduraooft (03-26-2009)

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Instyead of company_id, you could make two cols a PK such as company name and email. that pair would surely be uniqiue?

    Also, because you are likely now thinking of several users per company, you may want to have a login table, a compnay table and a third table for company_login

    Code:
    create table company_login
    ( company_name varchar(99) not null
    , email varchar(99) not null
    , login_id int not null
    )engine=.......
    then you can use a constraint for the fk's so that if a user leaves their company, and you remove them from the db, all records relative to them can also be removed automatically if your constraint uses ON DELETE CASCADE.

    Edit:
    referring back to your OP, you could blend the second and third tables into one because a usere is also a login person

    hth

    bazz
    Last edited by bazz; 03-26-2009 at 06:48 PM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by Fumigator View Post
    If you want my opinion, I don't think you should have a login for a company. Instead, carry different security levels for the users, and give some users admin access for an entire company.

    After all, the company cannot login and use your system; only people that own or work for a company can do that.
    I like that idea myself. I think it will provide more flexibility in your system in the future too. Your users table would have a column for the ID of the company they belong to and one for their security level.
    OracleGuy

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,860
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Wow..a right punch in the right area at the right time
    Many thanks to Fumigator. Your solution solves many other subsequent issues, which are purposefully avoided for simplicity in my OP, such as
    1. A company arrives and its short name is already used by normal user as his Username.
    2. When I make one of the user from a company as SuperAdmin(with more access rights, like managing different companies), then there will be a conflict in between the powers of this SuperAdmin user and his company. Something like Chicken or the egg


    Again, thanks for all.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •