0

I'm working on a project management app, where every company (which uses our app) will have a personal subdomain: company.domain.com. For many reasons I decided to use a separate database for each company.

Companies can invite people (by email) to work together on projects. A user can belong to multiple companies (so should appear in multiple databases). However, user data, and login information are stored in a seperate db because users use the same username, password for each company, and their first name, and last name is the same for each company.

What I do now, is the following:

  • there exists a separate database with: users, identities (user_id, username, password).
  • there also exists a users table in every database of each company, containing the same data about the user (actually only 3 fields: first name, last name, time zone).

Is there a better solution for this? If you have questions about the question :) please ask.

I'm using php + mysql.

Tamás Pap
  • 17,777
  • 15
  • 70
  • 102

1 Answers1

2

I don't think having the user data amongst more data tables is correct - think about redundancy...

If You have a database containing user data, why not having company databases linked to this database for user data?

A simple image: Example on how to divide users tables

Within Your companies databases You then can have additional company based user data within users table but the common data should remain only once per storage...

EDIT: ORA storage is just for example, You can use a MySQL database as well...

shadyyx
  • 15,825
  • 6
  • 60
  • 95
  • Exactly! The redundancy is the problem here. I don't want to store the same data twice. Thank you for your answer, and +1 for the schema. I'll make some benchmark test for multiple db selects, and cross db foreign keys. – Tamás Pap May 15 '12 at 10:49
  • To be honest I have never used `multidatabase` foreign keys (yet never had to) and If I would have to do this I would consider using MS SQL or better ORACLE for this, but it should be also wroking within MySQL: http://stackoverflow.com/questions/3905013/mysql-innodb-foreign-key-between-different-databases – shadyyx May 15 '12 at 11:01