FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Logic MySql





sonam
I need some suggestions about MySql. I need to create booking for one site. I will write scripts and this is not problem. I would like to see different ideas about tables in MySql. All my tables have building names: building1, building2, etc. and columns a1, a2, a3 (rooms names), price1, price2, price3 (price for different booking time), and this is working good. I have script what generate calendar but this script working with Flat File to get and save data. I must save all free/taken dates for each month in both of this and next year.

OK, now I need idea how to build one or two other tables and connect each other (join, inner join or whatever).

Sonam
Fire Boar
Hm, tricky that. Do you know why? It's because you haven't normalized your existing database structure. Specifically...

- Each building should have its own row in some table.
- Each room should have its own row in some table (linked to the buildings table by a foreign key).

About prices, is it a different price for each room? A different price for each time period? Or both? If the first, adding the price as a field in the rooms table should do it. If the second, a costs table should do the job, with "start time" and "end time" fields. If the third, make a costs table but have an extra field linked to the rooms table.

Now you simply need a table of bookings with a foreign key to the rooms table and start/end dates.
sonam
Yeah, I know it is tricky. Did you think I need few different tables and then connect each other with join. In most situation one house have four rooms and this rooms have different prices. For example:

HOTEL A:
Rooms | 1.1.-30-4|1.5.-30.-9.|1.10.-31.12|
RoomA| 30 | 40 | 30 |
RoomB| 35 | 45 | 35 |
RoomC| 40 | 50 | 45 |
RoomD| 30 | 40 | 35 |

HOTEL B:
Rooms | 1.1.-31-3|1.4.-31.-8.|1.9.-31.12 |
RoomA| 55 | 70 | 60 |
RoomB| 40 | 45 | 40 |
RoomC| 60 | 70 | 65 |
RoomD| 40 | 50 | 45 |

How you can see there is not rules any building can have own time and price. I need to create booking for each room in each hotel, calculate price for some period (new tricky if is reservation between two different prices) and display in calendar taken dates. Simple as cake Very Happy

Sonam
jmraker
I would store the data in 2 tables

building and room where
Code:

CREATE TABLE IF NOT EXISTS `building` (
  `building_id` int(11) NOT NULL auto_increment,
  `building_title` varchar(255),
  `building_num1` int(11),
  `building_num2` int(11),
  `building_num3` int(11),
  `building_num4` int(11),
  PRIMARY KEY  (`building_id`)
);

CREATE TABLE IF NOT EXISTS `room` (
  `room_id` int(11) NOT NULL auto_increment,
  `room_building_id` int(11),
  `room_title` varchar(255),
  `room_price1` int(11),
  `room_price2` int(11),
  `room_price3` int(11),
  `room_price4` int(11),
  PRIMARY KEY  (`room_id`)
);


All room data would be in one table and room_building_id tells you which building the record belongs to
Code:
SELECT * FROM building, room WHERE building_id=room_building_id
sonam
jmraker wrote:
I would store the data in 2 tables

building and room where
Code:

CREATE TABLE IF NOT EXISTS `building` (
  `building_id` int(11) NOT NULL auto_increment,
  `building_title` varchar(255),
  `building_num1` int(11),
  `building_num2` int(11),
  `building_num3` int(11),
  `building_num4` int(11),
  PRIMARY KEY  (`building_id`)
);

CREATE TABLE IF NOT EXISTS `room` (
  `room_id` int(11) NOT NULL auto_increment,
  `room_building_id` int(11),
  `room_title` varchar(255),
  `room_price1` int(11),
  `room_price2` int(11),
  `room_price3` int(11),
  `room_price4` int(11),
  PRIMARY KEY  (`room_id`)
);


All room data would be in one table and room_building_id tells you which building the record belongs to
Code:
SELECT * FROM building, room WHERE building_id=room_building_id


I was thinking in same way but here is one more problem calendar. In that case I need:

1. Two more table each for year (this and next) and with months where I will store taken dates (coma separated).

2. One table years and two table month (for each year) with days column.

3. One table years and months and two tables with days.

On the end I need idea how to store taken dates and connect with calendar?


Sonam

Sonam
Fire Boar
jmraker has a start, but it's still not normalized. If you've only got three fixed time periods, then this is better:

Code:
CREATE TABLE IF NOT EXISTS `building` (
  `id` int(11) NOT NULL auto_increment,
  `title` VARCHAR(255),
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `room` (
  `id` int(11) NOT NULL auto_increment
  `title` varchar(255),
  `price1` int(11),
  `price2` int(11),
  `price3` int(11),
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `building_room` (
  `b_id` int(11) NOT NULL,
  `r_id` int(11) NOT NULL,
  PRIMARY KEY (`b_id`, `r_id`)
);


Otherwise, consider removing price1, price2 and price3, then adding price and time_start and time_end to building_room, adding time_start to the primary key. For bookings, try

Code:
CREATE TABLE IF NOT EXISTS `booking` (
  `id` int(11) NOT NULL auto_increment,
  `room` int(11) NOT NULL auto_increment,
  `client` varchar(255),
  `start` timestamp,
  `end` timestamp,
  PRIMARY KEY (`id`)
);


To join booking data to a room, consider the following query:

Code:
SELECT b.client, b.start, b.end FROM room AS r LEFT JOIN booking AS b ON b.room = r.id WHERE r.title='Room 3';
sonam
Thanks a both. I will give a try, but in this moment I got one extra job for few days. :O

Sonam
Related topics
mysql connection question
php admin and mysql admin console
Mysql And PHP HELP PLZ
Do you use a CMS script?
Question about MySQL!
PHP, MySQL...
PHP Book (Php and Mysql for Dynamic Web Sites)
few mysql questions
E-Cards with PHP & MySQL
Logic Reasoning
HTTP AUTH with PHP and mySQL
Build an online dictionary by PHP/MySQL
Make search engine With PHP and mySQL, for your site
Hack to fix when MySQL crashes on Linux servers
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.