The best part, and the whole reason, for using our XML service is that it allows you to be flexible with what your client needs; such as data integration in to a proprietary backend, booking system or a custom CMS solution. We suggest you use the example schema as a guide only, and try integrating the provided data to meet your needs instead of building your solutions around it.

Below we list some of the less obvious relationships that are required for your integration to have accurate data output.

Cruises and specials are two separate products available within Cruise Factory, although cruises will share details with specials that have been created from them.

• Cruises are the base product within Cruise Factory. They contain the basic details of the cruise; description, itinerary, ship/cruise line/sailing dates, indicative pricing (priceguide) etc.
• Specials extend cruises by adding more layers of information; pre/post itinerary, detailed pricing, inclusions, specific sailing dates, etc.

The tables required to fetch the indicative pricing for a cruise are as follows:

• sailingdates
• priceguide

These tables are related via the sailingdates.id & priceguide.sailing_id fields. A query to fetch pricing for a particular cruise would look like this:

SELECT
	priceguide.`sailing_id`,
	priceguide.`inside_cabin`,
	priceguide.`outside_cabin`,
	priceguide.`balcony`,
	priceguide.`suite`
FROM
	priceguide,
	sailingdates
WHERE
	sailingdates.`id`=230484
	AND sailingdates.`id`=priceguide.`sailing_id`

This may seem confusing as the cruise ID is no where to be seen. Indicative pricing is based on sailing dates instead of only a cruise, so we must find the particular sailing date ID (sailingdates.id) of a cruise before we can fetch it's pricing.

To get a particular sailingdate.id of a cruise you will need the cruise.id and sailingdate.sailingdate parameters. You can then fetch the sailingdate.id via a query like this:

SELECT
	sailingdate.`id`
FROM
	sailingdates
WHERE
	sailingdates.`cruise_id`=19169
	AND sailingdates.`sailingdate`="2012-04-14"

This may seem convoluted but the example queries above are only to show you a basic way of fetching a single cruise's indicative pricing. More complex queries will make more sense in actual usage.

Specials have a field in the specials table called specials.start_price which will quickly give you the cheapest available price for that special. The only exception to that is when using multi-sailing date pricing (specialsmultipricing) as different dates may have different starting (lowest) prices.

The tables required to fetch pricing for specials can change depending on what type of pricing has been entered for that particular special. All tables that can have pricing for specials are as follows:

• leadpricing
• companionpricing
• specialspricing
  • cabins
• specialsmultipricing

You may notice that cabins is situated under the specialspricing table, this is because specialspricing has specific cabin pricing and uses the cabins table to provide the associated cabin's details.

Lead pricing is fetched for a specific special via the specials.id & leadpricing.special_id fields. For example:

SELECT
	leadpricing.`price_inside`,
	leadpricing.`price_outside`,
	leadpricing.`price_balcony`
	leadpricing.`price_suites`
FROM
	leadpricing
WHERE
	leadpricing.`special_id`=1234567

All sailing dates for this special (ID: 1234567) will have the same pricing when using the leadpricing table.

Companion pricing can also be included on a special with lead pricing available. So you will have to check the companionpricing table for extra pricing when looking at a special.

Similarly to the leadpricing query, we need to match the specials.id & companionpricing.special_id fields, as shown below:

SELECT
	companionpricing.`price_inside`,
	companionpricing.`price_outside`,
	companionpricing.`price_balcony`,
	companionpricing.`price_suites`
FROM
	companionpricing
WHERE
	companionpricing.`special_id`=1234567

Specials pricing is specific cabin pricing. It can also be included on a special with lead/companion pricing available, so you will have to check the specialspricing table for extra pricing when looking at a special in case it is available.

Specific cabin pricing is related via the specials.id parameter and the cabins.id field to the specialspricing.special_id & specialspricing.cabin_id fields. For example:

SELECT
	cabins.`name`,
	specialspricing.`price`
FROM
	specialspricing,
	cabins
WHERE
	specialspricing.`special_id`=648311
	AND specialspricing.`cabin_id`=cabins.`id`

As you can see we are no specifying inside, outside, balcony or suite in our select query. Instead we only fetch the name of the cabin that has pricing associated with it. This can return many results as multiple cabins can have pricing per special.

Multi-sailing date specials are the only specials that you will not need to check the other tables for. If a special has multi-sailing date pricing then you not have to check the other tables for extra pricing.

Multi-sailing date pricing is related via the specials.id and sailingdates.sailingdate parameters to the specialmultipricing table. In the query below we are fetching a single sailing date's pricing for a particular special.

SELECT
	specialmultipricing.`inside`,
	specialmultipricing.`outside`,
	specialmultipricing.`balcony`,
	specialmultipricing.`suite`
FROM
	specialmultipricing
WHERE
	specialmultipricing.`special_id`=580913
	AND specialmultipricing.`sailingdate`="2014-04-12"

Pre/post itineraries only relate to specials. Cruises do not need to look for these

Itineraries, or more specifically for this section, pre/post itineraries are rows of extra information that are used to extend the description of a cruise itinerary. Pre/post itineraries are located in the specialitineraries table and are related via the specials.id parameter to the specialitineraries.special_id field of the specialitineraries table. For example:

SELECT
	specialitineraries.`day`,
	specialitineraries.`activity`,
	specialitineraries.`starttime`,
	specialitineraries.`endtime`,
	specialitineraries.`type`,
	specialitineraries.`order`
FROM
	specialitineraries
WHERE
	specialitineraries.`special_id`=1234567
ORDER BY
	specialitineraries.`type` ASC,
	specialitineraries.`order` ASC

We include an ORDER BY clause in this query because it is makes it easier to format these for display. The specialitineraries.type field has two options; pre & post. This determines if they are to go before (pre) the cruise itinerary portion, or after (post). The specialitineraries.order field then determines the order they display in their given section (not the specialitineraries.day field).

The specialitineraries.day field will only accumulate for the post type of rows. The pre rows will always start at day 1 (one). If there is a pre itinerary, it will force the cruise itinerary's start day to be higher than 1 (however many days there are for the pre itinerary). The picture to the right best shows how the pre & post itineraries interface with the standard cruise itineraries. Notice that the post itinerary start date is the same day as the end date for the cruise itinerary (same goes for the cruise itinerary after the pre itinerary).