Nigel,

> Righto, lunch is over. Here's what I've done:
> Anyone like to add/modify/delete?
>
> /* Hotel Module
> Supports room information, restaurant bookings, cleaning and maintenance
> schedules
> */
>
> /* Room Types Source Table */
> CREATE TABLE tlkp_roomtype (
> str_type VARCHAR(20) NOT NULL,
> PRIMARY KEY(str_type)
> );
>
> /* Building Source Table
> (Just in case rooms are in different buildings )*/
> CREATE TABLE tbl_building (
> int_building NUMERIC(18,0) NOT NULL,
> str_name VARHCAR(50) NOT NULL,
> str_address VARCHAR(100),
> str_postcode VARCHAR(10),
> str_phone VARCHAR(12),
> str_fax VARCHAR(12),
> PRIMARY KEY(int_building),
> UNIQUE (str_name)
> );
>
> /* Company Setup Table */
> CREATE TABLE tbl_company (
> str_name VARCHAR(60) NOT NULL,
> str_address VARCHAR(100),
> str_billaddr VARCHAR(100), /* Billing Address */
> str_delivaddr VARCHAR(100), /* Delivery Address */
> str_postcode VARCHAR(6),
> str_city VARCHAR(50),
> str_state VARCHAR(50),
> str_country VARCHAR(50),
> str_phone VARCHAR(12),
> str_fax VARCHAR(12),
> str_web VARCHAR(30),
> str_email VARCHAR(30),
> PRIMARY KEY(str_name)
> );

Note that you cannot store two completely different
addresses in here.

I suggest removing the addresses part of the Company
from this table altogether and make it seperate table
Comp_Address and some kind of "address_code".

The same goes for fax/phone -> perhaps there should
be a seperate table for this is wel. You might have multiple
contact phone numbers for different departments.

> /* Rooms */
> CREATE TABLE tbl_rooms (
> int_room NUMERIC(18,0) NOT NULL,
> int_number INTEGER NOT NULL, /* Room number */
> int_floor INTEGER, /* Floor number */
> int_building NUMERIC(18,0), /* References tbl_building */
> str_type VARCHAR(20), /* References tlkp_roomtype */
> int_sleeps INTEGER, /* Sleeps how many people */
> int_singles INTEGER, /* Number of single beds */
> int_dbls INTEGER, /* Number of double beds */
> int_spare INTEGER, /* Spare beds(stretchers in cupboards) */
> ibl_aircon INTEGER, /* yes/no air conditioning */

Personally, I HATE flags... Depending on the number
of rows, searching for a specific flag might be slowish.

> ibl_tvvcr INTEGER, /* yes/no TV/VCR */
> ibl_tvdvd INTEGER, /* yes/no TV/DVD */
> ibl_spabath INTEGER, /* yes/no spa bath */
> ibl_shrebath INTEGER, /* Shared Bathroom (backpackers) */
> ibl_shrekitch INTEGER, /* Shared Kitchen (backpackers) */
> PRIMARY KEY(int_room),
> FOREIGN KEY(str_type) REFERENCES tlkp_roomtype (str_type) ON UPDATE
> CASCADE,
> FOREIGN KEY(int_building) REFERENCES tbl_building (int_building) ON
> UPDATE CASCADE
> );
> CREATE GENERATOR GEN_TBL_ROOM;
>
>
> /* Contacts */
> CREATE TABLE tbl_contact (
> int_contact NUMERIC(18,0) NOT NULL,
> str_lastname VARCHAR(50) NOT NULL,
> str_firstname VARCHAR(50),
> str_othernames VARCHAR(50),
> str_address VARCHAR(200),
> str_city VARCHAR(50),
> str_postcode (10),
> str_country VARCHAR(50), /* Nice to have a pulldown for this */

Make this a reference to table COUNTRY.

> str_phone VARCHAR(20),
> str_mobile VARCHAR(20),
> str_email VARCHAR(30),
> str_notes VARCHAR(200),
> PRIMARY KEY(int_contact)
> );
> CREATE GENERATOR GEN_TBL_CONTACT;
>
> /* Room Reservations */
> CREATE TABLE tbl_reservation (
> int_reservation NUMERIC(18,0) NOT NULL,
> int_room NUMERIC(18,0) NOT NULL, /* references tbl_room */
> dte_checkin DATE NOT NULL, /* The date fo checking */
> tme_checkin TIME NOT NULL DEFAULT '14:00', /* Time of checkin */
> dte_checkout DATE NOT NULL, /* The date leaving */
> tme_checkout TIME, /* The time leaving (Might want to hand around for a
> while) */
> PRIMARY KEY(int_reservation),
> FOREIGN KEY(int_room) REFERENCES tbl_room (int_room) ON UPDATE CASCADE
> );
> CREATE GENERATOR gen_tbl_reservation;
>
>
>
> /* Contact Notes */
> CREATE TABLE tlnk_contnote (
> int_note NUMERIC(18,0) NOT NULL,
> int_contact NUMERIC(18,0) NOT NULL,
> int_reservation NUMERIC(18,0),
> dte_date DATE NOT NULL DEFAULT 'now',
> tme_time TIME NOT NULL DEFAULT 'now',
> str_notes VARCHAR(5000) NOT NULL,
> PRIMARY KEY(int_note, int_contact),
> FOREIGN KEY(int_contact) REFERENCES tbl_contact (int_contact) ON

UPDATE

> CASCADE),
> FOREIGN KEY(int_reservation) REFERENCES tbl_reservation
> (int_reservation) ON UPDATE CASCADE
> );
> CREATE GENERATOR gen_tbl_contnote;
>
>
> /* Membership table */
> CREATE TABLE tbl_membership (
> int_group NUMERIC(18,0) NOT NULL,
> str_name VARCHAR(50) NOT NULL, /* The name of the group/club/thingo */
> int_contact NUMERIC(18,0) NOT NULL, /* References tbl_contact */
> dte_joined DATE, /* Date Joined */
> int_nights INTEGER, /* Nights stayed(reward calcs) */
> int_class VARCHAR(20), /* The class of this contact(cater for big
> spenders) */
> int_reward INTEGER, /* Reward Points */
> PRIMARY KEY(int_group),
> FOREIGN KEY(int_contact) REFERENCES tbl_contact (int_contact) ON

UPDATE

> CASCADE
> );
> CREATE GENERATOR GEN_TBL_MEMBERSHIP;
>
>
> /* Accounting Table */
> CREATE TABLE tbl_transaction (
> int_trans NUMERIC(18,0) NOT NULL,
> str_ttype VARCHAR(2) NOT NULL, /*

SL:Sale,PU:Purchase,RE:Rental,DP:Deposit

> */
> str_paytype VARCHAR(2) NOT NULL, /*
> RC:RoomCharge,CS:Cash,CC:CCard,DC:DebCard */
> int_invitem NUMERIC(18,0), /* Inventory item */
> int_fooditem NUMERIC(18,0), /* Restaurant item */
> int_movieitem NUMERIC(18,0), /* Pay for movie item */
> str_desc VARCHAR(200), /* Description of the item/service sold */
> flt_qty NUMERIC(18,2), /* Quantity of the above */
> flt_costamt NUMERIC(18,2), /* The internal cost of the item */
> flt_saleamt NUMERIC(18,2), /* The normal amount we sell it for */
> int_discount INTEGER, /* 0-100 discount percent */
> flt_finalamt NUMERIC(18,2), /* The final sale price(how much the person
> paid) */
> /* Put tax stuff here to suit your country */
> PRIMARY KEY(int_trans)
> );
> CREATE GENERATOR GEN_TBL_TRANSACTION;
>
> /* Food/Beverage POS */
> /* Just put a transaction in the above table
> with str_ttype='SL', and int_fooditem to suit*/
>
> /* Retail POS */
> /* Just put a transaction in the above table
> with str_ttype='SL'm and int_invtype to suit */
>
>
> /* Inventory */
> CREATE TABLE tbl_inventory (
> int_item NUMERIC(18,0) NOT NULL,
> str_name VARCHAR(20) NOT NULL,
> str_desc VARCHAR(100),
> flt_costamt NUMERIC(18,2), /* How much this costs us */
> flt_saleamt NUMERIC(18,2), /* How much we sell it for */
> PRIMARY KEY(int_item)
> );
> CREATE GENERATOR GEN_TBL_INVENTORY;
>
>
> /* Purchasing */
> /* Just put transactions in the trans table with tstr_ttype='PU' */

Flags. Bad.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com