how do I model this in database?

Question

I am using mysql as my backend database. Here is the use case;

A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. Each offering belongs to different types of variants:

• Hotels
• Origin city

For starters, I want to design the use case of Origin city:

There is a GOA package. It can be offered in 3 variants based on Origin city;

• From New Delhi to Goa
• From Bombay to Goa
• Goa itself ( Land package )

(Bold implies default selection)

a. and b. User can fly from either New Delhi or Bombay to GOA.
c. Goa is a Land package, where a user can reach Goa by himself and is a standalone holidayPackage.

Also, we need to further support the following use cases with a variant category (Origin City):

• one of the options in a variant can be marked as default by the admin, which will be a part of the offered inclusions (e.g. Bombay to Goa)

---

In the database, I need to model these relationships. Here is what I have in mind.

HolidayPackage has MANY to MANY relationship with Destination (read origin city). This will take care of options like:

• From New Delhi to Goa
• From Bombay to Goa

My concern:

• How do I handle Land packages only i.e. "Goa itself" and we don't have a travel leg ?
• How can I mark an option for a package, default at the database level ?

Answer 1

City
----
CityID
CityName
PRIMARY KEY (CityID)

Hotel
-----
HotelID
HotelName
CityID
PRIMARY KEY (HotelID)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)

and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

and default Package Version:

PackageDefaultVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

Answer 2

Here's my idea:

ResourceType: -- 2 rows: hotel, city
---------
ResourceTypeID not null autoincrement primary key 
ResourceType not null unique key

Resource: -- both hotels & cities
---------
ResourceID not null autoincrement primary key
ResourceTypeID not null foreign key references ResourceType (ResourceTypeID)
Name not null unique --can repeat the city name or be blank
City
StateOrProvince
PostalCode
Country

Package:
---------
PackageID not null autoincrement primary key
DestinationResourceID not null foreign key references Resource (ResourceID)
PackageName not null unique key

Offering:
---------
PackageID not null foreign key references Package (PackageID)
OfferingID not null autoincrement primary key 
OriginatingCityID null foreign key references Resource (ResourceID)

DefaultOffering
---------
PackageID not null unique key
OfferingID not null
foreign key (PackageID, OfferingID) references Offering (PackageID, OfferingID)

If a PK column can also be part of a multi-column FK (I just don't have time to check right now), then eliminate the DefaultOffering table and put DefaultOfferingID in the Package table with the same FK.

In the Offering table, you could replace OfferingID with an OfferingNumber that starts at 1 for each package. I would play around with it being unique, or primary key, or possibly make the primary key (PackageID, OfferingID). In SQL Server I would make the clustered index those two columns, but I am not experienced in mysql.

Regarding the Resource table, A hotel is in a city so it seems the only one to have many extra attributes. Leave inappropriate columns blank. If that is of concern, create a subtype Hotel table, and possibly also a subtype City table, similar to another answer here.

If the number of extra columns is not large, it may be worth not creating subtype tables in order to save a lot of complexity. A check constraint can enforce only the right type of data being stored for City & Hotel.

Don't let abstract ideals prevent you from practical design! I used to use NULLs for "no end date" but it caused awful complexity in my queries--and poor performance too when the OR condition forced a scan. Now I use '12/31/9999' and it is SO much better. When I first heard of a numbers table it felt dirty and wrong somehow, but now it is an indispensable basic tool.

One more thing: do you actually need a Package table? All it really does is hold a name, but can't the name be the destination city? You might be able to move ResourceID to Offering.DestinationResourceID. If that is impractical because the "destination" can be a Hotel rather than a City, you could rename OriginatingCityID to OriginatingResourceID and treat hotel stays as "trips" from the hotel to the city. Then the concept of Package is simply a collection of Offerings in the same city, and the Package name is simply the City name.

You haven't given much detail about how hotels will be handled, or info on the extra columns needed for cities and hotels. Please provide more detail if you would like a more structured answer on how to accommodate the data.

Answer 3

I find using integer flags the best for relational databases and for times when you have to meet conditions like you have explained. By setting the default origin to the ID of the city you want to default to, I believe you can achieve what you are after. Furthermore if in the holiday package the column "origin" is 0, you can assume that there is no travel leg and treat it as they are already at the destination.

An idea would be to do something like this:

CREATE TABLE `cities` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`city_name` VARCHAR(255) NOT NULL,
`flag` TINYINT(3) NOT NULL DEFAULT 0);

CREATE TABLE `hotels` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`hotel_name` VARCHAR(255) NOT NULL,
`city_id` INT UNSIGNED NOT NULL);

CREATE TABLE `HoldayPackage` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`origin` INT UNSIGNED NOT NULL default 0, //when origin=0, there is no travel leg, otherwise it's relative to the cities.id table
`destination` INT UNSIGNED NOT NULL DEFAULT 1, //foreign key to cities table again, using 1 as the default assuming the first entry you make is 'GOA'
`hotel` INT UNSIGNED NOT NULL, //foreign key to a hotels table
`flag` TINYINT(3) NOT NULL DEFAULT 0);

as a separate list of commands we can chagne the table types to innoDB (required for true relational databases) and their foreign keys:

alter table `cities` type=innodb;
alter table `hotels` type=innodb;
alter table `HoldayPackage` type=innodb;

alter table `HoldayPackage` add constraint destination_fk foreign key (destination) references cities(id) on update cascade on delete cascade;
alter table `hotels` add constraint city_fk foreign key (city_id) references cities(id) on update cascade on delete cascade;

I hope this is what you are looking for!