which database type are good for arrays?

Question

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 ?

Answer 1

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.

Answer 2

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.

Answer 3

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