Table Relationship Problems

[Basher]

Hello, and thank you for taking your time in reading this thread I made. I'm an IT student and simply a beginner in this kind of area.

My professor gave our class an assignment on Microsoft Access, wherein we are to make tables and queries using the SQL function. I've already created the tables required and have already started adding the required information. But, I encountered a problem. It seems that either I didn't correctly interpret how I made the tables correctly, or something that hasn't been taught yet. Whenever I insert a new value into the table GUEST_INFORMATION, it seems that I encounter an error, specifically about Key Violations. I've already done some research and it seems the foreign key is the cause. But, figuring the problem out is different from solving it. As you can see in this relationship that I've already assigned :

Table 2 : --------------------------------------------Table 1 :
​

| GUEST | ----------------------------------- | GUEST_INFORMATION || (P)GUEST_ID | ----------------------------- | (P)GUEST_INFO_ID | | (F)GUEST_INFO_ID | ----------------------- | (F)GUEST_ID |
| ROOM_ID | --------------------------------- | ROOM_NUM |
| NAME | ------------------------------------ | CHECK_IN_DATE |
| COMPANY | -------------------------------- | CHECK_OUT_DATE |
| ADDRESS | --------------------------------- | RESERVATION_DATE |
| CONTACT_NUM |

(P) = Primary Key
(F) = Foreign Key
GUEST and GUEST_INFORMATION are the names of the table and are not part of the columns.

-The two tables have two primary and foreign keys assigned in each other, therefore making a loop(?). As I try to insert a value, it tries to find the foreign key assigned to either one, but since they're empty, it only gives an error. Is there any way to fix this?

Also, as additional information, here's a part of the assignment that is needed to be done. I've interpret that the GUEST_INFO_ID under the table GUEST_INFORMATION is the primary key, while it's foreign key is assigned to the table GUEST, and vice versa.

Table 1 :
GUEST_INFORMATION
| GUEST_INFO_ID | GUEST_ID | ROOM_NUM | CHECK_IN_DATE | CHECK_OUT_DATE | RESERVATION_ID |


Table 2 :
GUEST
| GUEST_ID | GUEST_INFO_ID | ROOM_ID | NAME | COMPANY | ADDRESS | CONTACT_NUM |

Since I'm still new to the forums, I cannot post a printscreen of either the relationship table and the part of assignment. Instead, I've tried to make a graphical representation through text. Sorry for the inconvenience.

That's all that I can say about now. If you have any questions regarding about the information I've provided, please do so and I'll make it clearer for you.

Also, if you have a possible answer (an SQL code, perhaps), please make it as simple as possible, as I'm still new at Microsoft Access' SQL function.

Again, thank you for reading this thread.
​

 

[plog]

First, the names you have chosen for your objects are very confusing: GUEST_INFORMATION/GUEST tables, GUEST_INFO_ID/GUEST_ID/CONTACT_NUM and ROOM_ID/ROOM_NUM. If you had one or the other of those I could figure out what they did, but since you have more than one of what appears to be the same data, I have no idea what makes one object distinct from the other.

From a table level (GUEST_INFO and GUEST), tell me what each does. Try to use as little database jargon as possible, explain it in plain english.

 

[jdraw]

Have you studies Normalization yet? A little research on that may shed new light on your assignment.

Good luck.

 

[AbbottF]

FYI. When you have 2 table relate to eachother, they only need to share a single key. My guess is that GUEST.GUESTID will be linked to GUESTINFORMATION.GUESTID as a foreign key. GUEST.GUESTINFORMATIONID should be removed, since it is unnecessary.

 

[Basher]

Thank you for your replies, I've added attachments to the main post about the printscreen of the relationship and the assignment as well. If you have time, you can take a look at the assignment itself, to see if it's actually wrong.

I've checked it, and it seems GUEST_INFO_ID is only a redundant column. If you can confirm this, I will ask my professor regarding about it.

 

[AbbottF]

Pat,
I agree. This is not an structure that an experienced designer would use (ROOM_ID ? ROOM_NO). The names and relationships should more obvious and structured to teach, not confuse.