"Trips and Accommodation” need advice on table structure and relations in DB

Mycle · December 23, 2018, 6:43am

I have two tables

tblTrips which contains depart from AccommodationID and arrive AccommodationID, also fields for depart date, time and odometer, arrive date, time and odometer etc
tblAccommodation which contains details re accommodations which are used for business trips.

Can I have two ID (integer) fields in tblTrips DepartAccommodationID and ArriveAccommodationID
Will this cause problems?

Mycle

Ratslinger · December 23, 2018, 9:23pm

Hello,

This is based upon your using HSQLDB embedded (not noted but mentioned in previous post).

There is no problem in having multiple integers in one record pointing to another single table. You can use these fields to link forms & sub forms for data.

There is a bit of a problem in establishing relationships and thus data integrity. Base doesn’t want to co-operate using the graphical interface. You can do this using SQL and afterward it will be visible upon restarting the Base file:

You can create foreign keys using this SQL:

ALTER TABLE "YOUR_TABLE_NAME"  ADD CONSTRAINT FK_END FOREIGN KEY ("YOUR_FIELD") REFERENCES "REFERENCED_NAME"( ID )

where FK_END is the name used here for the constraint. A statement for the above image is:

ALTER TABLE "TripSectors"  ADD CONSTRAINT FK_END FOREIGN KEY ("IDEnd") REFERENCES "Accomodations"( ID )

While it is understood what your are trying to accomplish, the problem unanswered is the potential difference in the odometer reading from arrival to departure while at an Accommodation.

For further reference on this see → Multiple foreign keys to the same table.

Hope this helps.

Edit 2018-12-23:

With the test tables shown, the statement works as is:

However it works the same with the quotes:

This is because my field name is all caps. Use quotes if not all caps.

Ratslinger · December 23, 2018, 9:38pm

Note: The ALTER statement for the embedded DB must be run from menu Tools->SQL....

Mycle · December 23, 2018, 11:10pm

Thank you tested without success. Should the SQL be: ALTER TABLE “TripSectors” ADD CONSTRAINT FK_END FOREIGN KEY (“IDEnd”) REFERENCES “Accomodations”( “ID” )

ID in quotes??

Cheers,

Mike

Mycle · December 23, 2018, 11:38pm

Fantastic and thanks again, the DB is not crashing now.

Mike