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

Company_Id (a unique identification number, not sure from which table to take it right now)
Company short name(actually the Username for the company will be this value)
Company name
Email
Website
Address
Phone numbers
Maximum Intake


Similarly, a user will have

User_Id (again, to be taken from somewhere)
Company_Id (To relate a user with its company)
First name
Last name
Job title/Area of expertise
Email
Website
Address
Phone numbers


-----------------------
Now my plan is to have one table named Login having

Account_Id
Account_Type_Id (to differentiate normal user and company)
Username
Password
Email
Website
Address
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.


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


Sorry for this lengthy post and thanks for reading :)

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.

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


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.


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

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.

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

A company arrives and its short name is already used by normal user as his Username.
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 (http://en.wikipedia.org/wiki/Chicken-and-egg_problem) :)


Again, thanks for all.