Dear Community,
I am new to XANO but consider myself quite a good database engineer.
There is one database design scenario that might not work with XANO - and I just want to make sure if I am right or if I just didn't figure it out: Imagine the following design where the [Id] of the [Staff] table exists as a (blue) PRIMARY foreign key [StaffId] within the tables [StaffAvailability] and [StaffAttribute]. As a result the physical sorting order of the tables [StaffAvailability] and [StaffAttribute] are defined by the [StafffId] and there is no need for an additional index on the [StaffId] column. Of course this means that for every [Staff] entry there also need to be new entries for the tables [StaffAvailability] and [StaffAttribute].
PS: The green [StaffTypeId] exists a regular foreign key in the [Staff] table but this is not relevant to my question
Here comes my question: How to tell XANO's autogenerated id column (in tables [StaffAvailability] or [StaffAttribute]) to take the [Id] (autoincrement identity column) of another table (here: table [Staff])?
Consider it is not possible: As a result there is the need to use the internal [id] column as a primary key AND to add the [StaffId] column including an unique index [IX_StaffId_UQ] like this:
This leads to a little decreased INSERT-performance due to the additional index, a decreased SELECT-performance due to a JOIN on a foreign key again referencing and searching for the JSON-data … and more storage consumption.
Therefore just the simple question: Is it possible to achieve the first scenario? If not: Is the 2nd scenario the preferrable best practice?
@XANO community: Thank you very much in advance for your reply!!
Best regards, Daniel