Interesting Group Feature:
www.j-cook.pro/index.php/docs/versions/216-2-8-3

Currently we need to capture the locations of vehicles and users. Therefore we have a vehicles-table and a users-table. But it would be awful to have separate location tables. It would be better to have one table with a table_id and a table_name as a j-cook unique-group; all of which can be hand-coded outside the builder. But who wants to do that while living in a j-cook driven world?

The pic below shows an additional feature which would make linking multiple tables on a given foreign-key a breeze.


Hey Master Chef, Is there any possibility of ordering this addition and a bit of sole meuniere to go?

Warm regards
vlemos

That is a really bad database design. A foreign key is a (one) key to a (one) foreign table.

You should add a foreign key to the locations table in both vehicles and users table, not the other way around
So, you will have only 1 locations table, 1 users table and 1 vehicles table.

Thanks @Romkabouter for your feedback; however, not sure we understand each other, or maybe I should have explained my full requirements and how gps tracking works. In any case, I have seen compound-keys used in this way even in premium commercial extensions.

The idea of adding fks to the other tables won't work at all. It could not capture and map the multiple waypoints per vehicle or user. Maybe Big Chef or others would have a workable suggestion.

Personally, I can agree that not all scenarios merit this type of solution suite, but I see no real harm with its availability within the builder.

Warm regards
vlemos

Maybe on a high level, but I very highly doubt that a FK will be linked to multiple tables in a database design
I will probably be linked tables on a technical level (see below)

But in any case, if you want to map multiple point, you can add a linked table, like the hasMany relation.

- Create table locations
- Create table vehicles
- Create linked table vehicles_locations with FK to locations and FK to vehicles
- Create table users
- Create table users_locations to FK to locations and FK to users

This way a single location can be linked to multiple users and/or vehicles.
Does that suit your needs?

Have a look at the relations: www.j-cook.pro/index.php/r/relations

Yes, that would work as well but why do you think it is stronger than this:


I can see scenarios where the "1 table per fk" approach could get ungainly

Warm regards
vlemos

k++

PS: keep-up the feedback – we learn so much more when our ideas are challenged

It is nice to put it in an image like that, but from the locations point, there will be no telling what the reference is.
You will need, like you say, an extra field in the table to check what table the key is from. But that

While this can be done, you will always need code to check en verify this.
When you have a linked table, you will not have to check because it is implicit.
Also keeping referential integrity will be a pain.

Hi vlemos,

This idea is refused.
This is not coherent and unstable.
What happen when the same ID number is present in both vehicules and users ?
This FK would point to ID=4 (for instance), and you both have a user with ID=4 and a vehicule with ID=4.

If you want to do this, simply create 2 Foreign Key in your table and that's as simple as that. FK is not a required key, so it can stays null when your table row is pointing to the other table. Well, it is a wierd design, but it can work without problem.

On top, I would add in your table a field wich define the type (ENUM) wich says if it is a 'user', or a 'vehicule'.

Other possibility (maybe I ddn't got it right), is to extend your 'users' table to the 'vehicule' table. It could be a FK (1:N), or a 1:1. In both case, you instance your FK from 'users' to 'vehicule'. Or the opposite, as you like.

Correct ?