Hello I have a database like this
.
As you can see it is a Hotel database which is holding all the information about bookings,customers and rooms. The problem is with the bookings and BookDates tables. Every customer that makes a booking can book few dates and those don't have to be in any order. That's why I had to create BookDates in order to avoid this
But now I have the problem of how to relate those two tables as I can't just relate the part of compound key to the other table. That is how the BookDates table looks now. There is no primary key.
Asked
Active
Viewed 284 times
0
Michał Wesołowski
- 470
- 3
- 12
-
"how to relate those two tables". Which two tables? BookDates has BookDate as a foreign key into Booking. – Thilo Apr 29 '15 at 09:12
-
There is no BookDate field in the Booking, so I don't know what are you talking about. – Michał Wesołowski Apr 29 '15 at 09:15
-
Sorry, I meant BookNum. BookNum is the primary key of Booking and referred to from BookDates. – Thilo Apr 29 '15 at 12:19
2 Answers
0
If I understand your question correctly it can be formulated like this: which relations (foreign and primary keys) have to be created for the relations between tables BookDates and Bookings?
If that is the question I would say, create a primary key combining both fields in BookDates (the combination has to be unique). Bookings has primary key on BookNum. Create foreign key from field BookNum in BookDates to BookNum in Bookings.
One other point of attention: I would name my tables and fields singular. For example table BookDate, with fields BookNum and Date. Each record contains only one bookDate. When you retrieve multiple items from your table you have a list of BookDate records.
Aster
- 809
- 2
- 8
- 13
-
Yes you are correct and your answer seems to be correct as well. However I have tried to do that and I don't know how to do it as it doesn't let me to make Compound key (Dates & BookNum) along side Foreign key (BookNum). – Michał Wesołowski Apr 29 '15 at 11:51
-
Where do you want to implement it? (MySql, Sql server, something else?) What is the error you receive? What is the script you try to run? – Aster Apr 30 '15 at 06:03
0
In the ER diagram, for BookDates table, I see that you already have marked BookNum as PK & FK. FK is fine, but using BookNum field as PK will not work since BookNum could be repeated and cannot be unique. You can consider the following options:
Create a composite primary key on BookNum & BookDates
Add a new BookDatesId as a primary key, which could be an Identity column and make that as PK, it would be the "Surrogate Key".
Vasan
- 375
- 1
- 12