CODEWITHSUNDEEP
databasedatabase-design
stUrb
stUrb

Reputation: 6832

Database design for a reservation system with assets which can travel between locations

I'm developing a database structure which consists of assets which travel between a couple of locations. I want to make a reservation system for these assets. If assets don't travel between the different locations it's very easy to get the availability: just check how many reservations for a certain day exists and substract them from the normally available number assets.

available = Normal.Available - already reserved

But the complicating factor is the fact people can travel between different locations. A customer can pick up an asset at Location A and drop it of at Location B. For instance pickup on september 13th 10:00am dropoff at LocB september 13th 13:00.

If I now want to have the available assets at Location B at 13:00 this is the normal available number +1 because of the asset which travelled from A->B. The availability at location A is one less then normal, obviously.

How can I graph these movements in a database structure?
The entities are clear: Assets, locations, bookings and Customers. The difficulty lies within getting the availability at the different locations at different times.

Upvotes: 2

Views: 1184

Answers (3)

Benny Hill
Benny Hill

Reputation: 6240

assets
    id              unsigned int(P)
    description     varchar(200)

+----+-------------+
| id | description |
+----+-------------+
|  1 | Widget A    |
| .. | ........... |
+----+-------------+

See PHP's crypt() function for hashing the password.

customers
    id              unsigned int(P)
    first_name      varchar(50)
    middle_name     varchar(50) // Allow NULL
    last_name       varchar(50)
    email           varchar(255)
    username        varchar(32)
    password        varbinary(255) // hashed
    ...

+----+------------+-------------+-----------+----------------------------+-----------+----------+-----+
| id | first_name | middle_name | last_name | email                      | username  | password | ... |
+----+------------+-------------+-----------+----------------------------+-----------+----------+-----+
|  1 | John       | Quincy      | Public    | [email protected]            | johnqball | xxxxxxxx | ... |
|  2 | Jane       | NULL        | Doe       | [email protected] | janeykins | xxxxxxxx | ... |
| .. | .......... | ........... | ......... | .......................... | ......... | .......  | ... |
+----+------------+-------------+-----------+----------------------------+-----------+----------+-----+

locations
    id              unsigned int(P)
    description     varchar(200)

+----+-------------+
| id | description |
+----+-------------+
|  1 | Facility A  |
|  2 | Facility B  |
| .. | ........... |
+----+-------------+

reservations
    id              unsigned int(P)
    asset_id        unsigned int(F assets.id)
    customer_id     unsigned int(F customers.id)
    from_id         unsigned int(F locations.id)
    to_id           unsigned int(F locations.id)
    beg             datetime
    end             datetime

+----+-------------+----------+---------+-------+---------------------+---------------------+
| id | customer_id | asset_id | from_id | to_id |         beg         |         end         |
+----+-------------+----------+---------+-------+---------------------+---------------------+
|  1 |           1 |        1 |       1 |     2 | 2013-09-13 03:00:00 | 2013-09-13 14:00:00 |
|  1 |           1 |        1 |       2 |     1 | 2013-09-14 19:00:00 | 2013-09-15 07:00:00 |
|  1 |           1 |        1 |       1 |     2 | 2013-09-15 10:00:00 | 2013-09-15 17:00:00 |
|  1 |           1 |        1 |       2 |     1 | 2013-09-16 08:00:00 | 2013-09-16 13:00:00 |
|  1 |           1 |        1 |       1 |     2 | 2013-09-17 10:00:00 | 2013-09-17 17:00:00 |
| .. | ........... | ........ | ....... | ..... | ................... | ................... |
+----+-------------+----------+---------+-------+---------------------+---------------------+

To find out what's available right now at Facility A:

SELECT DISTINCT asset_id, * FROM reservations
WHERE to_id = 1 AND
    beg > NOW()
ORDER BY beg, end

To find out what's available tomorrow at 15:00 at Facility B:

$target_datetime = '2013-09-14 15:00:00';
SELECT DISTINCT asset_id, * FROM reservations
WHERE to_id = 2 AND
    beg > $target_datetime
ORDER BY beg, end

Upvotes: 2

Tassadaque
Tassadaque

Reputation: 8199

if you maintaining each asset availability as asset Instance then you may create a table AssetInstances(AssetInstanceID,AssetID)

the reservation table would be 

AssetMovement(AssetInstanceID, FromLocationID,ToLocationID,StartMovementTime,EndMovementTime,CustomerID)

Now ToLocation is the current place of instance you can get the location wise instance count based on max(movementtime) group by AssetID ToLocationID will be null in case of asset is in transit

In case you are maintaining one asset per row there is no instance table then AssetMovement table may be like 

AssetMovement(AssetID, FromLocationID,ToLocationID,StartMovementTime,EndMovementTime,CustomerID,IsCurrentReservation)

Now if asset is picked by customer from location A IsCurrenReservation will be true and when he drop and location B IsCurrenReservation still remains true. Now say it is again picked from Location B then IsCurrenReservation will be true for this and previous entery's IsCurrenReservation will be false. Now in query with IsCurrenReservation= true will give you the current number of assets per location. and if TolocationID is null and IsCurrentReservation=true will give you number of reservation at any time. This may not be an ideal solution but looks like it will work

Upvotes: 1

Frank Goortani
Frank Goortani

Reputation: 1435

What I see is 4 entities to start with : Asset, Location, Customer and Booking. Use Booking to show what time what asset is booked by a customer from one location to another.

Upvotes: 0

Related Questions