Design Question 1

[playsted]

Hey,
I have encountered a problem in designing my database. The database is meant to track Hotel Deals; however, there are two different types of hotel deals, deals involving single hotels and deals involving a portfolio. I want to have a table that tracks deals, and then a table that relates to the seperate hotel information. Without the portfolio complication this is easy; however, I also must account for portfolios in my deal information. Furthermore, I want to include the specific hotels from the hotel table in the portfolios. Do you guys have any suggestios about how to set the relationship up so that it can include both portfolios and hotels in an orderly fashion?




Andy

 

[SkipVought]

Hi,

What are the characteristics of a Portfolio?

What are the characteristics of a Hotel?

How are they similar?

How do they relate?

What are the characteristics of a HotelDeal?

How do these all relate?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[willir]

Difficult situation Andy

Your problem sounds similiar to a contact database or skill set or escalation database where an attribute can refer to a specific person or a group of people.

You will have missing values where the "deal" may or may not be stored with a specific hotel.

Perhaps reversing the the perspective and approach the problem from the "deal". I think a many-to-many design may be best...

tblDeal
DealID - primary key
DealName
ValidFromDate
ValidToDate
...etc, qualities that better define the Deal (price, discount, etc)

tblHotel
HotelID - primary key
HotelName
HotelChainCode
Location
Address
City
...etc

Then tie it together...

tblHotelDeal
HotelID - foreign key to tblHotel
DealID - foreign key to tblDeal

Primary key = HotelID + DealID

You may want to add more fields to this table that may be specific to the Hotel and Deal

What about the "Portfolio" ?

Anytime, there is more than one hotel that has the same deal, it may be part of a portfolio. But perhaps it may be better to define the portfolio.

Can a hotel belong to more than one portfolio? Then you have a m:n relationship...

tblPortfolio
PortfolioID - pk
PortfolioName
...etc

tblPortfolioHotel
PortfolioID - fk to tblPortfolio
HotelID - fk to tblHotel

If a hotel can only belong to one portfolio, then just add the PortfolioID foreign key to tblHotel.

tblHotel
HotelID - primary key
HotelName
PortfolioID - foreign key to tblPortfolio
HotelChainCode
Location
Address
City

Do you see the problem? Although you may adjust the price or "deal" in one location, you have to maintain both the portfolio table, if m:n, and the tblHotelDeal.

Anyway, hopefully, this has given you some ideas.

Richard

 

[playsted]

Thank you so much, I basically came up with that soloution myself using the many to many relationships that you suggested. Sorry, it took so long for me to reply I was working on another project and forgot I had posted

Andy