Mysql – how to model this in database

database-designerdMySQL

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 ?

Best Answer

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)
Related Question