Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?
share|improve this question
add comment

migrated from stackoverflow.com Mar 8 '12 at 17:48

This question came from our site for professional and enthusiast programmers.

3 Answers

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!

share|improve this answer
    
There is a default per each variant. So, a default Origin city, a default hotel selection etc. Also, over years, I've been advised over and over against using magic numbers (origin=0; no default leg). –  brainydexter Mar 1 '12 at 5:20
    
hehe, I know it can get confusing and has definitely caused me some pain in the past. I've just been accustomed to program this way because as silly as it may be, I am OCD about writing code that will be as fast as possible, using numbers also uses less in terms of storage :) –  Ben Ashton Mar 1 '12 at 8:18
    
@brainydexter Using origin = 0 is perfectly valid. If it helps, enter a city "No Origin" or "No Travel" as 0 and then FK to it. What's the problem with it? Just because you've been told doesn't mean it's true. There are all sorts of things that get repeated as inviolable rules, except in the final analysis they were never true. –  ErikE Mar 22 '12 at 4:33
add comment
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)
share|improve this answer
    
With this design, will we not be duplicating a lot of information twice ? What is the use for PackageType field in table PackageVersion when we have specific tables for LandPackageVersion and FlightPackageVersion and so on ? Can you also please add some comments about the design ? Thanks –  brainydexter Mar 4 '12 at 14:33
    
@brainydexter: The only duplication is the Primary Keys which are duplicated (as Foriegn Keys) in other tables. I'll update the answer with more details. –  ypercube Mar 4 '12 at 19:48
    
I was hoping you would update the answer with more details.. Thanks –  brainydexter Mar 9 '12 at 13:10
    
Another thing I'd like to add: Why do we need PackageType, since that is apparent from the table we reference it ? Perhaps, I'm not thinking straight on how we are going to retrieve the variants? Please help :) –  brainydexter Mar 9 '12 at 13:26
1  
@brainydexter The PackageType column present in the subtype tables (LandPackageVersion, FlightPackageVersion), by being part of the FK to the supertype table (PakcageVersion) enforces that a package version can only be in one of the tables at once. –  ErikE Mar 22 '12 at 4:30
show 3 more comments

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.

share|improve this answer
    
How would you use the table Resource for both Hotel and cities since each have very different attributes ? –  brainydexter Mar 22 '12 at 9:47
    
Please see update. –  ErikE Mar 22 '12 at 15:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.