Schema Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jimbo - 18 Aug 2004 20:17 GMT

Please help.

Situation - I have 2000 assets. Each asset is idenified by a unique location
code. I want users to be able to book these assets on a daily, weekly,
monthly or a user defined time. Users should be able to book assets
according to their location. Once a general location has been identified, I
would like to show the availablity of a number of assets over a given
period. Once the user has selected the asset and the period this asset is no
longer available for this period.

So far I have drawn a blank as to how the Database schema should go
together, the only option I have come up with is to create 365 date entries
for each asset, which is far from practical as that would create 730,000
entries.

Is there a way I can create two tables that will cross reference each other
and will simplify the amount of entries. I plan to put this schema on either
MySQL or Oracle, dependant on its size.

Your thoughts on schema layout would be greatly appreciated.

Bootneck

Reply to this Message

Laconic2 - 18 Aug 2004 20:32 GMT

> Please help.
>
[quoted text clipped - 18 lines]
>
> Bootneck

I don't get it. If you generated a table with 730,000 rows in it, you
wouldn't even make a desktop database like MS Access start breathing hard.

There are more compact ways of expressing the same data, but you may not
even need them.

Reply to this Message

Barry - 19 Aug 2004 13:53 GMT

> Please help.
>
> Situation - I have 2000 assets. Each asset is idenified by a unique location
> code. I want users to be able to book these assets on a daily, weekly,
> monthly or a user defined time.

>>Your thoughts on schema layout would be greatly appreciated.

This page on my Database Answers web site lists 14 Data Models for
different kinds of Reservations :-
http://www.databaseanswers.org/data_models/index.htm
Here's one for Hotel Reservations which is a good place to start :-
http://www.databaseanswers.org/data_models/hotels/index.htm

This Cinema Bookings Model shows two alternative Tables to support
queries :-
http://www.databaseanswers.org/data_models/cinema_bookings/index.htm

HTH

Barry Williams
Principal Consultant
Database Answers

Reply to this Message

--CELKO-- - 24 Aug 2004 01:43 GMT

>>. Each asset is idenified by a unique location code. I want users to

be able to book these assets on a daily, weekly, monthly or a user
defined time. Users should be able to book assets according to their
location. <<

Create a Bookings table something like this:

CREATE TABLE Bookings
(location_id INTEGER NOT NULL,
customer_id INTEGER DEFAULT 0 NOT NULL, -- zero is available
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_date TIMESTAMP, -- null means currently in use
CHECK (start_date < finish_date),
PRIMARY KEY (location_id, start_date),
...);

>> Once a general location has been identified, I would like to show

the availablity of a number of assets over a given period. Once the
user has selected the asset and the period this asset is no longer
available for this period. <<

load up a table of locations picked by the customer.

SELECT :my_start_date, :my_finish_date, location_id
FROM Bookings AS B1
WHERE location_id IN (SELECT location_id FROM CustomerLocs)
AND :my_start_date BETWEEN start_date
AND COALESCE(finish_date, CURRENT_TIMESTAMP)
AND :my_finish_date BETWEEN start_date
AND COALESCE(finish_date, CURRENT_TIMESTAMP)
GROUP BY B1.location_id
HAVING SUM (customer_id) = 0 ;

Reply to this Message