Type Relationship Flexibility Enhancement

Ideas & Features
1

I am trying to understand Fibery from a Relational Database (RD) perspective. From this angle I view a Fibery Type with fields as a RD Entity with attributes. The relationships between the types/entities are similar.

It appears to me as if the Fibery Name Field is fixed as the “Primary Key” in every Type.
If I am correct with the analogy explained above, It would really enhance the options to be able to select and define which field of a Type are the primary key or secondary key for use by another type as primary key through a relationship.

Traditionally I am not a database developer, so forgive me if I am totally missing the point.

2

Your description makes sense, but can you provide cases where it can be helpful?

3

Building on the acknowledgement that I understand Fibery correctly as confirmed in the reply, the following should then also be applicable to Fibery:

In a relational database, a relationship is formed by correlating rows belonging to different tables. A table relationship is established when a child table defines a Foreign Key column that references the Primary Key column of its parent table.

Every database table relationship is, therefore, built on top of Foreign Key columns, and there can be three table relationship types:

  • one-to-many is the most common relationship, and it associates a row from a parent table to multiple rows in a child table.
  • one-to-one requires the child table Primary Key to be associated via a Foreign Key with the parent table Primary Key column.
  • many-to-many requires a link table containing two Foreign Key columns that reference the two different parent tables.

This appears partially true for Fibery in the existence of the relationship types. However it seems as if in Fibery the Primary Key for every type has been hard coded as the “Name” Field for that specific type. It appears as if the Foreign Key is not used.

If this is true and if it is possible to enhance this functionality, I believe that it would be possible to have the same flexibility as available in relational database design in Fibery. This could open the development capabilities in Fibery to use existing relational design techniques and methods to mere mortals that are not hard core database developers like the ones used to develop Fibery.

4

In Fibery you can have all these relations. You have automatic back-reference for all kind of relations. So I am still not following the benefits you might get if we allow to define other fields as Primary Key.

5

Would it be possible to implement the following in Fibery specifcially related to the primary and foreign key relationships and where Customers, Orders and Employees are Fibery Types with their unique attribures or fields and one to may relationships between the primary and foreign keys:

hs0431

6

I don’t know if this is what @NicolSpies was thinking about, but to answer the question as to

then I can see that being able to use a field other than the Name field as the ‘primary key’ allows a user to see different information in places where currently only the name shows (e.g. when using search or when trying to find the correct entity to link to in a relation).
As a concrete example, the issue recently discussed:

The user could create a formula field that concatenates Product + Release and then define this as the ‘primary key’.
Of course, this particular problem will be solved when the Name field can contain a formula (which I know is in the works) but I can imagine other similar scenarios.
FWIW I think Coda allows the user to choose which field is the display name for an entity, so I guess they think this is useful for some of their users.

As I said though, I don’t know if this is what the original question related to, since I have no real idea what is referred to when saying

7

If you imagine that in Fibery that

  • the Name field for Customers type = Customer ID
  • the Name field for Orders type = Order Number
  • the Name field for Employee type = Employee Number
    then what you’re asking for is exactly what Fibery does.

Is your need simply to be able to choose a different data type for the Name field and a different name for the Name field?

If so, it seems like this is just a different mental representation of the same thing…

8

This is exactly as you described, if I could choose a data type and name for the name field, I am actually defining my primary key as is normally done in relational database design.

As it is now I need to remember the Name field is actually not a name but actually Customer ID which is also chosen as the primary key that uniquely defines the entry/row/record in the Customers Data Table called a Type in Fibery.

9

In nearest future (next week?) we will release a possibility to set a formula for the Name field, in this case you will be able to specify Name as Customer ID and see id value. I think it will solve your problem

1 Like
10

Yes, that will be great, if I understand the functionality correctly,

I will be able to “map” Customer ID to the Name field using a formula which will effectively define the Customer ID as primary key and I would be able to change the name of the name field to Customer ID. The data type of the name field will then not be limited to text anymore but to the data type of the result of the formula.

11

I want to clear something up here, and preface with the fact that I am not a database developer myself and may have this wrong. That said I think the use of the term “primary key” here is incorrect. The “primary key” is the unique identifier used to refer to each record. The name fields in Fibery are not forced to be unique, and so could not be used this way. I am drawing my understanding of Primary Keys from Wikipedia, among other sources: Primary key - Wikipedia

If I’m not mistaken there is a truly unique identifier for each record (entity). Some type of “unique” identifier can be seen in the upper-left of Entity views and reflected in the URL schema, although I suspect there are more sophisticated (and truly unique) identifiers behind-the-scenes, internal to Fibery.

For example, I have a task: “Once proforma is finalized, check against operating agreement ownership, discuss if needed”
This is #121 as identified at the top-left of the Entity view.
The URL to this task is this, in part:

…fibery.io/Project_Management/Task/Once-proforma-is-finalized,-check-against-operating-agreement-ownership-and-discuss-if-needed-121

I can change the name of it and it doesn’t mess up any relationships to this entity, the URL changes but keeps the 121 (minor name change for illustration purposes only):

…fibery.io/Project_Management/Task/Once-proforma-is-finalized,-check-against-operating-agreement-ownership,-discuss-if-needed-121

I am guessing that the numeric identifiers may be unique within each type, but you may have overlapping numbers in other types. I’m not certain of that, but it seems to be the case for some tests on my end.

So I just point all this out because I do think having common understanding of the terminology is critical to correctly expressing and interpreting the needs outlined here. And I don’t think the real issue in this case is what field is being used as a “primary key”, but rather about what fields are displayed, and (relatedly) how things are named. On what basis they are associated does not seem to be the problem, as far as I can see. Unless I’m misunderstanding, which is entirely possible. :smiley:

Would appreciate some correction/clarification from @mdubakov or someone else on the Fibery team as to how Fibery’s unique IDs/primary keys may work, if different from what I’ve stated above.

12

Indeed. I used quotes for ‘primary key’ for this and other reasons :slight_smile:

I am not a database specialist either which is why I tried to focus on the manifestation of the problem/solution rather than the underlying implementation. But it’s good that you point it out.

13

I apologize for the philosophical “essay” below. It is part of my attempt as a newcomer to Fibery to get to know the new toy. Here it goes…

I believe behind the scenes Fibery exists of all the building blocks used in a relational database as the database layer. An interface layer has been developed where the business rules reside. The business rules are what makes Fibery. This business rules in the interface layer allows us to do with Fibery what we do and what makes it unique and different from a traditional relational database. It also allows the user to do things that is not allowed in a normal database environment. Some of the possibilities have resemblance to traditional database rules.

All of the above moves Fibery into the “no code” environment against the formal database development environment only accessible to hard core database developers, developing software for users like us who need a unique system specific to own business requirements.

It is immensely powerful, I would dare to say, a new paradigm in software “development”. This new approach might be too foreign for traditional software developers and totally acceptable for newcomers to software development. I however, fall somewhere in between.

The power of Fibery is to “grow” your unique system as your needs grow. Coming from a systems engineering background, I like to plan the system up front to some extent in order to have a “Designed for but not yet fitted with” system that is scalable by design.

My intention is not to, hypothetically get under the bonnet of Fibery to tweak the engine, but to gain understanding of what it is and what it is not in order to develop my own set of design/implementation rules unique to Fibery in order to maximize the full potential of this amazing tool.

PS. I founded a company 15 years ago and started using TargetProcess from its early releases. As a formal software development company we had to “hack” into its database using complex queries to generate the reports we needed. Everything was “hard coded” for a agile software development environment. Fibery has broken that by providing a system where nothing is hard coded allowing true creative freedom for the system user/developer.

1 Like
14

All of that is true and is the reason I am here as well. :slight_smile: I just wanted to make sure that you understood that names are not actually acting as a primary key because it has functional consequences for how you build things.

15

Basically @NicolSpies, it sounds like you simply want to be able to rename the “Name” field to be whatever name you choose. Seems that would solve your concern, yes?

16

Thanks, your clarification answered the question. I was definitely on the wrong track by bringing the primary key construct into play that would have had negative consequences going forward.

17

Yes, but more than that. The indication from Michael that it would soon be possible to use a formula in the name field, would provide a very nice feature allowing the name field to be used with the automatic rule based linking of relationships.

18

Agreed. Will be great.

19

Hi!
Glad to say, that it is possible to use a formula in the name field, hope, that would help :sparkles:

More details here :face_with_monocle:

20

Love It !! :smiley:

1 Like