I have following tables:
How will I handle the billing?
Billing so far:
If I were you I would make sure to separate all of the data. Is this going to be completely controlled in the database, or is there going to be an application? If it is an application I would have a users table and permissions tables for the people using the application. I would do a separate a customers table for your customers. I agree with the availability table, but if you are using a price based system I would store that separately. For billing I would set up a few tables. I would have a payment type table and a transaction table with due dates. I like the idea of a check in and out table as well.
I have done one structure that may help you.
room. It's the main room configuration where you can add any value that refer to the rooms.
room_status. It's the room status where you could know which is the room actual status.
transaction. Its your main table, here you're going to storage all the activities of the rooms.
user. Here you can delimit which user is who.
You should make a try and add the indexes in the fields your going to filter in your queries.
Tables:
CREATE TABLE `room` (
`id` int(6) NOT NULL,
`roomName` varchar(75) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`status` int(2) DEFAULT NULL,
`othersfields` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `room_status` (
`idroom_status` int(2) NOT NULL,
`sName` varchar(45) DEFAULT NULL,
PRIMARY KEY (`idroom_status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `transaction` (
`id` int(6) NOT NULL,
`room_id` int(6) DEFAULT NULL,
`check_in` datetime DEFAULT NULL,
`check_out` datetime DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`othersfields` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`fName` varchar(45) DEFAULT NULL,
`lName` varchar(45) DEFAULT NULL,
`pwd` varchar(45) DEFAULT NULL,
`rol` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Information:
mysql> SELECT * FROM test.room;
+----+----------+---------------------+--------+--------------+
| id | roomName | date | status | othersfields |
+----+----------+---------------------+--------+--------------+
| 1 | Suit | 2015-07-13 00:00:00 | 1 | NULL |
| 2 | Normal | NULL | 2 | NULL |
| 3 | Other | NULL | 1 | NULL |
+----+----------+---------------------+--------+--------------+
3 rows in set (0.00 sec)
mysql> select * from test.room_status;
+---------------+-------------+
| idroom_status | sName |
+---------------+-------------+
| 1 | Open |
| 2 | Closed |
| 3 | Maintenance |
+---------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from test.user;
+----+-------+-------+------------+------+
| id | fName | lName | pwd | rol |
+----+-------+-------+------------+------+
| 1 | oNare | Test | 123ka21312 | NULL |
+----+-------+-------+------------+------+
1 row in set (0.00 sec)
mysql> select * from test.transaction;
+----+---------+---------------------+---------------------+---------+--------------+
| id | room_id | check_in | check_out | user_id | othersfields |
+----+---------+---------------------+---------------------+---------+--------------+
| 1 | 1 | 2015-07-01 00:00:00 | 2015-07-02 00:00:00 | 1 | NULL |
| 2 | 1 | 2015-07-03 00:00:00 | 2015-07-05 00:00:00 | 1 | NULL |
| 3 | 2 | 2015-07-03 00:00:00 | 2015-07-04 00:00:00 | 1 | NULL |
| 4 | 1 | 2015-07-13 00:00:00 | NULL | 1 | NULL |
| 5 | 2 | 2015-07-09 00:00:00 | 2015-07-13 00:00:00 | 1 | NULL |
+----+---------+---------------------+---------------------+---------+--------------+
5 rows in set (0.00 sec)
mysql>
Test query:
mysql> SELECT
-> r.id AS idRoom,
-> r.roomName,
-> rs.sName as Status,
-> t.check_in,
-> t.check_out
-> FROM test.transaction AS t
-> JOIN test.room AS r ON (t.room_id=r.id)
-> JOIN test.room_status AS rs ON (rs.idroom_status=r.status)
-> JOIN test.user AS u ON (t.user_id=u.id);
+--------+----------+--------+---------------------+---------------------+
| idRoom | roomName | Status | check_in | check_out |
+--------+----------+--------+---------------------+---------------------+
| 1 | Suit | Open | 2015-07-01 00:00:00 | 2015-07-02 00:00:00 |
| 1 | Suit | Open | 2015-07-03 00:00:00 | 2015-07-05 00:00:00 |
| 1 | Suit | Open | 2015-07-13 00:00:00 | NULL |
| 2 | Normal | Closed | 2015-07-03 00:00:00 | 2015-07-04 00:00:00 |
| 2 | Normal | Closed | 2015-07-09 00:00:00 | 2015-07-13 00:00:00 |
+--------+----------+--------+---------------------+---------------------+
5 rows in set (0.00 sec)
mysql>
id_room on your table availability so you can JOIN room.id with availability.id_room easily.
billing_id into transaction table because transaction is your master table, you could JOIN it with every table.
I will use pseudo code to illustrate; you can adapt to your DBMS's specific syntax.
Join availability to transaction on room.id and also on the overlapping intervals i.e.
Availability.from <= TimePartOf(transaction.checkout)
And availability.til >= TimePartOf(transaction.checkin)
If, for example, a room had different rates in the morning and afternoon and your transaction went from 11am to 2pm the above would now show two rows. Some simple DateDiff() maths will give you the duration of each billable rate i.e. 11am to noon and noon to 2pm. Multiply by the rate, SUM() the result and GROUP BY the user id.
You will need a flag on transaction to show which rows have been billed and which not.
Edit: on further reflection it looks like a row in the billing table is a single invoice (i.e. bill) set to a user. Rather than add a flag to transaction add the foreign key to billing. Then you will know not only that a transaction has been billed but exactly when and how. Note that billing systems, invoicing and payment is a specialist subject in itself and full of intricacies.
