[SOLVED] Brainstorming a data model. I need some thoughts.

[jlange]

Alright, here's the setup:

 

I'm writing a reservation system for my school's fleet of iBook (and now MacBook, but that's unimportant) carts. I have a user database all set up with id numbers and everything, so that's not an issue. What I need help with is developing a way to store the reservation data. The carts will need to be reserved for specific periods during a day, and will have to be able to be reserved for more than one timeslot, as you can book these carts up to two weeks in the future. I'm trying to think of a way to do this, but I'm drawing a blank. Can one put a whole PHP array in a single cell of a table? This is version 2.0 of the system; previously I was using a hierarchy of text files and folders to store the data (pain in the rear, if you ask me)

 

Thanks much,

jlange

 

ps., go easy on me, I'm new to all this shiny MySQL stuff.

[Wildbug]

One idea would be to store reservations as a date and a 8 (or 12 or 24) digit bitstring.  In this way, reservations that already exist can easily be tested for via the bitwise AND function.

 

For example:

CREATE TABLE reservations (
id INT UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id INT UNSIGNED ZEROFILL,
unit_id TINYINT UNSIGNED,
date DATE,
hours SMALLINT UNSIGNED
);

# A user reserves Unit #1 for 8am-12pm August 1st:
INSERT INTO reservations VALUES(NULL, 1, 1, 20070801, b'001111000000');

# Another user tries to reserve the unit for August 1st 10-1pm:
SELECT b'000011110000' & hours AS reserved FROM reservations WHERE date=20070801 AND unit_id=1;
+----------+
| reserved |
+----------+
|      192 |
+----------+
1 row in set (0.00 sec)

Anything not equal to zero means there's a conflict in times.

You can also find out which hours conflict:
mysql> SELECT LPAD(BIN(b'000011110000' & hours),12,'0') AS reserved FROM reservations WHERE date=20070801 AND unit_id=1;
+--------------+
| reserved     |
+--------------+
| 000011000000 |
+--------------+
1 row in set (0.00 sec)

000011110000 Original reservation
001111000000 Desired reservation
000011000000 Conflicts via & (bitwise AND)

The 12 bits are equivalent to these times (in my example):
1 6am
2
3
4 9am
5
6
7 12pm
8
9
10
11
12 5pm

 

I guess you could also use MySQL's SET column type for the same kind of operations, but you get the idea -- bitwise AND is a powerful way to find time conflicts without having to resort to complex time and date calcuations/ranges.

[jlange]

Very cool. I think I'm going to take a simpler approach, and make a table for reservations that will have the date (m, d, and y) along with the id# of the object that's being reserved (referencing to another table), the id# of the faculty member who is checking it out, and finally the class period of the day for which its reserved. Making it time-based would be so much easier, but alas my school doesn't believe in making things easy.

 

It's a work in progress and I'll update as I go along in case anyone wants to hear. I'm on Twitter; you'll probably find a lot of status updates going out today. (twitter.com/joey)

[Wildbug]

Here's an example using the SET column.  (Just replace the hours with your school periods, 1st, 2nd, or whatever.)

 

CREATE TABLE reservations (
id INT UNSIGNED ZEROFILL AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id INT UNSIGNED ZEROFILL,
unit_id TINYINT UNSIGNED,
date DATE,
hours SET(
	'0', '1', '2', '3', '4', '5',
	'6', '7', '8', '9', '10', '11',
	'12', '13', '14', '15', '16', '17',
	'18', '19', '20', '21', '22', '23'
)
);

INSERT INTO reservations VALUES
(NULL,1,1,20070801,'8,9,10,11'),
(NULL,2,1,20070801,'14,15,16');

mysql> SELECT * FROM reservations;
+------------+------------+---------+------------+-----------+
| id         | user_id    | unit_id | date       | hours     |
+------------+------------+---------+------------+-----------+
| 0000000001 | 0000000001 |       1 | 2007-08-01 | 8,9,10,11 |
| 0000000002 | 0000000002 |       1 | 2007-08-01 | 14,15,16  |
+------------+------------+---------+------------+-----------+
2 rows in set (0.00 sec)

# Finding conflicts with bitwise AND and the SET column:
mysql> SELECT * FROM reservations WHERE hours & b'000000000011100000000000';
+------------+------------+---------+------------+-----------+
| id         | user_id    | unit_id | date       | hours     |
+------------+------------+---------+------------+-----------+
| 0000000001 | 0000000001 |       1 | 2007-08-01 | 8,9,10,11 |
+------------+------------+---------+------------+-----------+
1 row in set (0.00 sec)

 

The key is that you need to have discrete blocks of time instead of times like 13:06-16:55 and then trying to figure out if 14:32-19:15 overlapped with it, so I think that using class periods would be much easier than using arbitrary times.

 

You'll need to write a PHP function to generate the bitstring (trivial).

 

Good luck with your project.

[jlange]

Thanks! Have a great day!