Welcome, Guest
Username: Password: Remember me

TOPIC: Add a second table in the FK

Add a second table in the FK 13 Jun 2016 05:21 #14036

  • vlemos
  • vlemos's Avatar
  • Online
  • Elite Member
  • Posts: 295
  • Thank you received: 41
  • Karma: 21
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? :lol:

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? :whistle:

Warm regards
vlemos
The administrator has disabled public write access.

Add a second table in the FK 13 Jun 2016 07:58 #14037

  • Romkabouter
  • Romkabouter's Avatar
  • Offline
  • Elite Member
  • Posts: 310
  • Thank you received: 131
  • Karma: 48
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.
Last Edit: 13 Jun 2016 07:59 by Romkabouter.
The administrator has disabled public write access.
The following user(s) said Thank You: vlemos

Add a second table in the FK 13 Jun 2016 12:00 #14042

  • vlemos
  • vlemos's Avatar
  • Online
  • Elite Member
  • Posts: 295
  • Thank you received: 41
  • Karma: 21
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
The administrator has disabled public write access.

Add a second table in the FK 13 Jun 2016 12:19 #14043

  • Romkabouter
  • Romkabouter's Avatar
  • Offline
  • Elite Member
  • Posts: 310
  • Thank you received: 131
  • Karma: 48
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
The administrator has disabled public write access.
The following user(s) said Thank You: admin, vlemos

Add a second table in the FK 13 Jun 2016 13:39 #14049

  • vlemos
  • vlemos's Avatar
  • Online
  • Elite Member
  • Posts: 295
  • Thank you received: 41
  • Karma: 21
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 ;)
The administrator has disabled public write access.

Add a second table in the FK 13 Jun 2016 14:13 #14051

  • Romkabouter
  • Romkabouter's Avatar
  • Offline
  • Elite Member
  • Posts: 310
  • Thank you received: 131
  • Karma: 48
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.
The administrator has disabled public write access.
The following user(s) said Thank You: vlemos

Add a second table in the FK 13 Jun 2016 15:46 #14054

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3712
  • Thank you received: 987
  • Karma: 140
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 ?
Coding is now a piece of cake
The administrator has disabled public write access.
The following user(s) said Thank You: vlemos
Time to create page: 0.162 seconds

Get Started