Multiple tables for a single foreign key

Question

I'm currently making a database for my java web app for Tourism.

Only register user can book a tour but can take along several people. For this, I separate into User table and Guest table, each with its own primary key.

Everything is set so far, but when it comes to making my BookRoomDetail table, I have to fill in which person for which slot in the room. the problem arises when both register user and guest can fill this slot, and they're from 2 different tables.

Screenshot

How do I set the foreign key(or anything else) for this?

Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. If you give an image, include a legend/key/explanation. Insert images using edit functions. This is a faq. Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal and/or error message, with & without your particular strings/names, & read many answers. If you post, use one phrasing as title. — philipxy, Mar 19 '19 at 09:56
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Mar 19 '19 at 10:00

score 0 · Answer 1 · answered Mar 19 '19 at 06:44

0

As I got your problem, you can add one more field for MainUser in your tblBookRoom Detail as a foreign key. And whenever any user books room you can add his/her primary key and guest primary key in forwhom field.

 **tblBookRoomDetail**
      ID (Primary key Of Table)
      RoomId 
      For Whom (For MainUser or Guest)
      MainUser (MainUser Primary Key Who is doing this reservation)
      Slot
      FromDate
      ToDate
      BookId

answered Mar 19 '19 at 06:44

So room for `MainUser` will have `ForWhom` null? – Piro Mar 19 '19 at 07:01
@Piro it will also have MainUser primaryKey. MainUser primary key in tblBookRoomDetail is for reference. – Mar 19 '19 at 07:03

score 0 · Answer 2 · answered Mar 19 '19 at 07:15

This is not possible to insert two different primary key value in a column of other table, if you have relation in them.

You can do this without the making the relation in them. But it's not a good process because if the both key value same in this case you can't identify the actual.

If you want to achieve this goal then you should have a table tblAllUsers which primary key is also primary key of the tblGuest and tblUser and then you can make relation to tblBookRoomDetail table direct to tblAllUsers. In this case you can differentiate the Guest user or Registered Users.

or

Can Create the two different columns in tblBookRoomDetail, one for Guest user and second for Registered user.

Multiple tables for a single foreign key

2 Answers2