CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
lightning_missile
lightning_missile

Reputation: 2992

Creating relationship between table column and another database

Say I have an application that adds companies. I have a table like this:

create table companies(
    id primary key,
    name varchar
)

For each company, I have to store loads of business-type information. Since they are so many, I've decided to create one database per company to avoid collision, very slow performance and complicated queries. The database name would be the company name in my companies table.

My problem is I would like to give the company name and the database a one-to-one relationship so they would be in sink with each other. Is that possible? If not, is there a better approach besides creating a database per company?

Upvotes: 0

Views: 129

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This is an elaboration on my comment.

Databases are designed to handle tables with millions, even billions of rows. I am guessing that your data is not that big.

Why do you want to store all the data for a single entity in a single table? Here are some reasons:

  • You can readily run queries across different companies.
  • You can define foreign key relationships between entities.
  • If you change the data structure, you can do it in one place.
  • It can be much more efficient in terms of space. Databases generally store data on data pages, and partially filled pages will eat up lots of space.
  • You have a single database for backup and recovery purposes.
  • A where clause to select a single company's data is not particularly "complicated".

(Note: This is referring to "entities", a database term. Data for the companies can still be spread across multiple tables.)

For performance, you can then adjust the data model, add indexes, and partition tables. This is sufficient for the vast myriad of applications that run on databases.

There are a handful of situations where a separate database per company/client is needed. Here are some I've encountered:

  1. You are told this is what you have to do.
  2. Each client really is customized, so there is little common data structure among them.
  3. Security requirements specify that data must be in different databases or even on different servers (this is "good reason" for 1.).

Upvotes: 1

Related Questions