Using Multiple Databases

Question

A company is hired by another company for helping in a certain field.

So I created the following tables: Companies: id, company name, company address Administrators: (in relation with companies) id, company_id, username, email, password, fullname

Then, each company has some workers in it, I store data about workers. Hence, workers has a profession, Agreement Type signed and some other common things.

Now, the parent tables and data in it for workers (Agreement Types, Professions, Other Common Things) are going to be the same for each company.

Should I create 1 new database for each company? Or store All data into the same database?

Thanks.

Answer 1

Since "Agreement Types", "Professions" are going to be same for each company, I would suggest to have a lookup table like "AgreementTypes" with columns such as "ID", "Type" and refer "ID" column in "Workers" table. I don't think new database is required, relational databases are used to eliminate data redundancy and create appropriate relationships between entities.

By imagining having one database for one company, it ends up with having one record in "Company" table in each database. "Administrators" & "Workers" are associated with that single record. And other common entities such as "AgreementTypes" will be in other tables.

So, if there is any addition/modification to agreement type, it is difficult to do it in all databases. Similarly, if there is any new entity to be linked to "Company" entity, again all databases needs to be revisited based on assumption that these entities belong to ONE application.

Answer 2

You should have one single database, with a structure something like this (this is somewhat over-simplified, but you get the idea):

Companies
    CompanyID PK
    CompanyName
    CompanyAddress
    OtherCompanySpecificData

Workers
    WorkerID PK
    CompanyID FK
    LastName
    FirstName
    DOB
    AgreementTypeID FK
    ProfessionID FK
    UserID FK - A worker may need more than one user account
    Other UserSpecificData

Professions
    ProfessionID PK
    Profession
    OtherProfessionStuff

AgreementType
    AgreementTypeID PK
    AgreementTypeName
    Description
    OtherAgreementStuff

Users
    UserID PK -- A Worker may need more than 1 user account
    WorkerID FK
    UserName
    Password
    AccountStatus

Groups
    GroupID PK
    GroupName
    OtherGroupSpecificData

UserGroups --Composite Key with UserID and GroupID
    UserID PK 
    GroupID PK

Obviously, things will grow a little more complex, and I don't know your requirements or business model. For example, if companies can have different departments, you may wish to create a CompanyDepartment table, and then be able to assign workers to various departments.

And so on.

The more atomic you can make your data structures, the more flexible your database will be as it grows. Google the term Database Normalization, and specifically the Third Normal Form (3NF) for a database (Considered the minimum for efficient database design).

Hope that helps. Feel free to elaborate if you are stuck - there is a lot of great help here on SO.