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.
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:
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
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
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?