3

I have a relational database similar to the one in the diagram where there is one table called app instance which is the foreign key in almost all other tables in the database. It is meant to represent instances of the same web app.

Essentially if I need to run a test version of the app, I could create a new record in the app instance table. Then all records in the other tables would reference the test app instance keeping the data for the various app instances separate.

Having an app instance table makes it easy to reference all objects related to that app without going through various tables to get to the objects.

Here is the situation I'm dealing with shown in a crow's foot diagram with which I hope to illustrate what I think is a misuse of the idea:

Example explaining the issue

The app instance table is used to represent a group/franchise of stores. Although there are connections from the app instance table to all the other tables, I have left it out of the diagram. A connection from sale to app instance makes sense if I want to query all sales made by a group of stores owned by the same franchise/entity.

However, the other connections to app instance are questionable. items sold in one group's stores can easily be sold in another, the same with the brands. They do not belong to one app instance only.

I think that the right way would be to have separate group and app instance tables. Where app instance keeps ALL the data separate from another instance in the same database.

But in that case, why not just use multiple databases? Is there ever a use case for having one table that references all the other tables in this fashion? Is it possible that having a app instance table would have unwanted effects if it is in the database but not used? Or would there be unwanted effects if only one app instance is ever created?

  • 1
    It's funny. By removing all the connections from "app instance" to the other tables, you've created a properly normalized data model, which until you notice unresolvable performance problems, is the answer you are looking for. Database views can squash a normalized data model into something more easy to query. – Greg Burghardt Jul 10 at 12:30
  • 1
    Your title should be "one table that is referenced by all the others", if indeed the foreign keys are i n all the other tables. – Walter Mitty Jul 11 at 0:32
  • Yup changed it. I was thinking about belongs to relationships. – simple_code Jul 11 at 5:12
  • 1
    What is the purpose of those references to the app instance table? Do you have different web app instances, and each manages its own set of data? – Doc Brown Jul 11 at 5:46
  • 1
    The relationship between the "Sale" and "Item" is probably reversed. A "Sale" promotes zero, 1 or more "item(s)". – NoChance Jul 11 at 22:53
1

The alternative to this design would probably what you already suggested: giving each web app instance a database or database instance of its own. So this is a trade-off between these two design approaches.

Benefits of the multiple-database approach:

  • better separation from the perspective of safety and security (if one app instance corrupts its database because of a bug, other instances are not affected; backup-recovery can be done per instance)

  • easier scaling - databases can be easier distributed to different machines, if necessary

  • the DB schema is simpler and does not cause the confusions you observed

  • it is easier to run different app instance versions in parallel when they require different database schemas

Benefits of the single-database approach:

  • backup can be done for all app instances in one step (note that "recovery" must be done in one step, too, which is not necessarily a benefit)

  • cross-instance queries are simpler to implement

  • cross-instance data (like a common user table) can be probably managed easier (in a multiple-database approach, one needs usually a separate database instance for this)

  • setting up and running multiple database instances may require more effort and costs for certain kind of databases. This depends heavily on the kind of DBMS involved.

  • Thanks for the answer @Doc Brown +1. I see you have listed benefits of either approach and your answer answers the first question of the 4 that I have asked. – simple_code Jul 12 at 9:28
  • 1
    @simple_code: I think my answer also fits to your second question: use cases are cross-instance queries, and situations where creating separate databases are costly. – Doc Brown Jul 12 at 10:03
  • 1
    ... to your third and fourth question: anything is possible (maybe the web application contains a bug why it won't work with just one record in the app instance table, maybe some dev has build such nonsense into the app because of a misunderstanding, who knows). But if the web app is build sensibly, it should not matter. – Doc Brown Jul 12 at 10:07
  • I see what you're saying and you're right. In my case using an app instance table is definitely wrong. But the effects of leaving it in should be negligible. – simple_code Jul 12 at 10:16
  • In terms of one table that gets referenced by many others, I think a user table reference using a created by or edited by field from the other tables makes sense. Databases are designed like this all the time to keep user's data separate. – simple_code Jul 12 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.