What do you do when you need data from two schemas in this case? For example, say you want to get a product from a catalog schema and its creator or editor from a users shema. Things could get complicate fast. One of the things that joins and associations do well is sorting, linking, and organising data.
abitdodgy
0
anthonator
What’s hard with this approach is drawing your boundaries. If you have two tables that rely on each other then they may belong within the same application. If you feel like you need to create a join between two applications that could be a signal that your boundary is incorrect.
1
abitdodgy
I think it’s an impossible task to draw your boundaries objectively since there is no single criteria that can tell you right from wrong. For example, I work for a multichannel commerce company. We have several business systems that could be thought of as coupled or independent. This is an extremely oversimplified example:
- A PIM (product information management) system for registering and distributing product data to a number of other applications.
- An e-commerce website.
- User management for said applications.
The PIM is used to create and enrich product data. From poster descriptions, to label information, all the way to web descriptions and digital assets. The PIM is responsible for a hundred or so attributes and only a couple of dozen of those are used on the e-commerce website.
But, fundamentally, they both make use of the same data. Products and prices, along with other tables specific to each system. They also share the same users.
If you separate them, you would have to duplicate and then synchronise shared data. If you couple them, then you would be writing two fundamentally different systems that happen to share a subset of data.
Whether you decide to decouple them entirely or within an umbrella doesn’t matter. An umbrella merely brings some conveniences. You said you treat schemas as separate databases. So, in this case, your approach would be to have a products table in both apps, a user table in both apps, etc… you might run into issues of session management, which user to authenticate, track, and and all sorts of other stuff, but I don’t know if these drawbacks are that serious.
There isn’t an easy answer here. Coupling the schemas could reduce duplication, but perhaps it’s not worth the hassle.
PS: We actually have an e-commerce, estimates, internal sales, and telesales systems. We also have an ERP connector, users, BI automation, marketing communication with suppliers, shipping app and api, customer registration app, and more. Sometimes you can say it’s all related. E-commerce needs customers and products. So do internal sales systems. So does an estimates app.
Deciding whether all this stuff belongs to the same app or in separate apps and databases is tricky.
1
chouzar
Sounds like a current design I’m dealing with (just a toy project), basically I have an app that prints documents and a CRUD app which manages users. I need some of the user information to “stamp” the document and print it, so the document app contains both document and user schemas.
| User App | Document App | |
|---|---|---|
| Document schema | ||
| User schema | —> | Sign Schema |
Yes, this means that data is duplicated and inconsistent between apps; this is fine, the document app will retrieve user info and transform it into a “sign schema” which is later stored into its own document database.
For practical purposes all schema relationships will be kept exclusively to the bounds of the app, my only SQL joins will be between the “document” and “sign” tables. It may be a naive strategy for the medium or long term but I like how this design is settling down.
1