DB: How to create a PRIMARY(!) Foreign Key Relationship?

raycoon

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

arturosanz

If I'm not mistaken, in the first scenario there are one-to-one relationships between the Staff and StaffAvailability tables and the Staff and StaffAttribute tables. This means there is only one record in the StaffAvailability and in the StaffAttribute tables per each record in the Staff table. Am I correct?

If so, there is no need to use the StaffAvailability and StaffAttribute tables because you can substitute them by complex object fields types in the Staff table. You will have everything tidy this way too.

Xano will always create an internal integer id primary key field in all tables which you can't delete.