Specific vs generic database types

Get Help
1

This is a classic database structure problem, I’m wondering about how to approach in Fibery. Am thinking about the example of Organisations. There are a couple of approaches:

  1. A single Organisation database that could contain customers, partners, competitors, suppliers, all types of roles.

  2. Separate databases for Customer, Competitor and then general Organisation.

The Fibery templates lean towards 2 I think. But then how to handle issues like:

  • What if an Org is both a competitor and potential partner?
  • Is it better to strive for one record to represent that entity? (I would think yes), then how to choose location?
  • Does separate dbs create more of a proliferation of “Relation” fields? How to manage that? (maybe thats where using inline text links is better?)

Any insights or patterns that have worked well for others would be great to hear about!

2 Likes
2

This is always a hard problem. So far we are reluctant to add “is a” concept (inheritance) into Fibery, since it may complicate the system enormously. There are few heuristics that might help to decide:

  1. If there are MANY different fields, then it is better to have different Databases. For example, if a Competitor has 5-10 unique fields, it is maybe better to have it as a separate DB
  2. Duplication might be better than unification, since you can’t always foresee how data will evolve, and different DBs have better flexibility to handle it.
  3. You may try to create an umbrella DB (Organization) that will have 1-1 link to Customer and Competitor, and setup automated rules or lookups to fetch data from Customers/Competitor into Organization. It’s a lot of work and it should have good arguments to go for it.
  4. If you decide that Competitors and Customers should live in a single DB, the best way to differentiate them is just add a single select field with DB Type. Note that you will have to filter by this field in many places, so it may become more burden.

This problem might be solved in future with polymorphic relations. It’s hard to implement and we will dig into it somewhere in Q3 2022 (not a promise).

3 Likes