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?

Improve this question
5
  • 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. Commented Jul 10, 2019 at 12:30
  • 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? Commented Jul 11, 2019 at 5:46
  • Moreover, what DBMS is used to run this system? Is it a heavyweight commercial one like Oracle, where creation of new database instances might cause a certain effort and cost? Or a lightweight like Sqlite? Commented Jul 11, 2019 at 5:49
  • 1
    The relationship between the "Sale" and "Item" is probably reversed. A "Sale" promotes zero, 1 or more "item(s)". Commented Jul 11, 2019 at 22:53
  • The idea behind sale and item in this design is that 1 kind of item can get sold many times. The design is a bit simplistic thanks @NoChance. Maybe a sale can consist of many items and a particular kind of item can be sold many times. Commented Jul 12, 2019 at 9:21

1 Answer 1

Reset to default
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.

Improve this answer
6
  • 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. Commented Jul 12, 2019 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. Commented Jul 12, 2019 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. Commented Jul 12, 2019 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. Commented Jul 12, 2019 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. Commented Jul 12, 2019 at 10:19

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.