1

I am designing a inventory web application for data centers. All the metadata that the application persists and displays is always grouped by a data center. For e.g. The list of servers, number of environments (logical grouping of servers), customers etc are always to be displayed for a data center. The application is suppose to track assets across various data centers.

I would like to get suggestions on how should I persist my database model in order to achieve this. A couple of approaches I had in mind

  1. Add a data_center_id column in all the metadata tables (we use a relational database) included in the application. When displaying the metadata apply a default filter for data_center_id. Drawback - This doesn't seem to be transparent approach and requires every metadata table to store the data center id.
  2. Have different metadata tables per data center. This might be too much since the amount of data persisted is not huge.

Any other better suggestions/ideas?

Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113

1 Answers1

0

I think I would have a database for each data center and each database would contain the same tables (servers, customers, environments, etc.):

DATABASE 1 (for data center 1)

customers
    id              unsigned int(P)
    name            varchar(50)
    ...

servers
    id              unsigned int(P)
    model_id        unsigned int(F models.id)
    purchased       date
    ...

etc.

DATABASE 2 (for data center 2)

customers
    id              unsigned int(P)
    name            varchar(50)
    ...

servers
    id              unsigned int(P)
    model_id        unsigned int(F models.id)
    purchased       date
    ...

etc.

Then in my application logic I would just give the user the ability to look at different data centers. All of your queries will be the same, they will just need to point to the database associated with the selected data center.

Of course someone will want to be able to query ALL the databases to get an idea of how many servers your organization owns, etc. So you'll have to write separate application code to handle that kind of administrative/executive level stuff.

In other words, I think you've narrowed it down to your two possible choices and I think your choice #2 is the way I would go.

Benny Hill
  • 6,191
  • 4
  • 39
  • 59
  • Option 2 also means creation of the database at runtime when a data center is added. Any other ideas apart from these 2? – Andy Dufresne Sep 06 '13 at 04:59
  • @AndyDufresne without being more intimately involved with your organization, no I can't think of any other options. [This question](http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database) may be helpful for copying a MySQL database. – Benny Hill Sep 06 '13 at 13:01