views:

81

answers:

3

i want my database to support one company haveing many users how can i do that?
example

users table(UID,username,password)
company table(CID,companyname,usersthatistheownerofthecompany)

how can i do that? what should i do ? should i make an array in php like 1241,423,4123 *uid's that will be inserted on usersthatistheownerofthecompany row ? or is there any better idea ?

+9  A: 

If you want each user to have one (and never more) company, you should have :

  • user table
    • uid
    • username
    • ...
    • company_id
  • company table
    • company_id
    • company_name
    • ...

Then, user.company_id would be a foreign key, that references company.company_id.

And, then, you store :

  • One line in user for each user
    • Referencing the id of the right company for that user
    • which is the company_id of the right line in the company table.
  • And one line for each company in company

There is no user's related information stored in the company table -- and as each user "points" to a company, a company can have several users.


Storing data as an array like you suggested is definitely not quite a good idea -- just not the way a relational database works.


If each user can have several companies, and each comparny can have several users, you'll have to add a third table (a join table), that will indicate, for each user(s), to which company(ies) they are attached to :

  • user table
    • uid
    • username
    • ...
  • company table
    • company_id
    • company_name
    • ...
  • user_company table
    • uid
    • company_id

In this situation, there is no user-related stuff in the company table, and there is no company-related stuff in the user table : the link between those is in the user_company table.

Of course, user_company.uid will be a foreign-key to user.uid ; and user_company.company_id will be a foreign-key to company.company_id.

Pascal MARTIN
pascal when do you sleep? hehe, great answers. how about many to many ?
Adam Ramadhan
@Adam : well, right now it's 5:30 pm here, so not the time to sleep ;-) ;;; for the many-to-many part, you'll have to use a join table, like described in the second part of my answer *(which I've edited, so you might not have seen that part before posting your comment)*
Pascal MARTIN
Adam Ramadhan
the "cascade" thing can be done by your database server *(depends on the server, though)* ; but I prefer doing that myself in my application's code, and not using that feature of the DB engine ; that way, I know what's being updated/deleted ;;; and cascade doesn't have much to do with many-to-many : many-to-many means that each user can have several companies, and each companies can have several users.
Pascal MARTIN
btw in user_company which one will be PK ? company_id ? uid ? or there are no PK ? and are PK already unique ? or we better put a UQ to a PK ?
Adam Ramadhan
The PK is the identifier of a row ; so, it has to be unique (it is) ;; in user_company, the identifier is the couple user/company ; so, the primary_key will be on those two fields (you can have several fields in an index ; a PK is a specific kind of index)
Pascal MARTIN
+1  A: 

There is a better idea - it's called a cross-table join. What you do is you create a third table, which contains two columns. In those two columns you store the primary key of the tables you're connecting to eachother.

The idea is that you're creating a relation between a company and a user. In a relational database, relations are indicated between tables by using foreign keys.

Of course, this only applies when you want to connect multiple users to multiple companies (an "M-N" relationship). If you want to connect multiple users to a single company, simply add a column for the company id to the user.

kander
A: 

Any relational database is a good way to go. Have a look at MS SQL or MySQL.

rochal