Ask Your Question
0

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

asked 2018-12-23 07:43:14 +0100

Mycle gravatar image

I have two tables 1. tblTrips which contains depart from AccommodationID and arrive AccommodationID, also fields for depart date, time and odometer, arrive date, time and odometer etc 2. 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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-12-23 22:23:38 +0100

Ratslinger gravatar image

updated 2018-12-24 00:24:25 +0100

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:

image description

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:

image description

However it works the same with the quotes:

image description

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

edit flag offensive delete link more

Comments

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

Ratslinger gravatar imageRatslinger ( 2018-12-23 22:38:54 +0100 )edit

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 gravatar imageMycle ( 2018-12-24 00:10:09 +0100 )edit
0

answered 2018-12-24 00:38:45 +0100

Mycle gravatar image

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

Mike

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-23 07:43:14 +0100

Seen: 58 times

Last updated: Dec 24 '18