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 ?
views:
81answers:
3
+9
A:
If you want each user to have one (and never more) company, you should have :
usertableuidusername- ...
company_id
companytablecompany_idcompany_name- ...
Then, user.company_id would be a foreign key, that references company.company_id.
And, then, you store :
- One line in
userfor each user- Referencing the id of the right company for that user
- which is the
company_idof the right line in thecompanytable.
- 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 :
usertableuidusername- ...
companytablecompany_idcompany_name- ...
user_companytableuidcompany_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
2010-07-18 15:21:13
pascal when do you sleep? hehe, great answers. how about many to many ?
Adam Ramadhan
2010-07-18 15:27:51
@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
2010-07-18 15:31:59
Adam Ramadhan
2010-07-18 15:51:03
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
2010-07-18 15:57:06
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
2010-07-21 09:55:20
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
2010-07-21 17:15:17
+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
2010-07-18 15:21:32