Rental modelling Take 2

So based on past feedback here is my second approach to address rentals. Again this is a minimalist approach with the idea that more features can be added later.

1. Fixed assets form the base. What is rented is essentially a fixed asset or a piece of one.

2. A fixed asset is mapped to 0 or more "Rentals." A rental is an asset which can be discretely rented out.

3. A rental is mapped to one or more "Schedules" which is a combination of a duration and a service. The system itself will not provide duration management for schedules (this will be expected in the first iteration to be manually entered in interval format, i.e. '4 hours' or '1 week').

4. A schedule is mapped to any number of "Rental Contracts" which

represent a given customer rental.

So here is the proposed db schema and notes for further iterations:

create table rental (

id serial primary key,

asset_id int not null references asset_item(id),

rental_number text not null,

obsolete bool not null default false

);

create unique index active_rental_number_idx on rental (rental_number)

where not obsolete;

create table schedule (

id serial not null unique,

rental_id int references rental(id),

label text,

duration interval not null,

parts_id int,

primary key(rental_id, label),

unique (schedule_id, rental_id) -- used for fkey

);

-- Future iterations will probably have a duration table for

-- managing drop downs of durations. Not sure if it should be an

-- fkey or not.

create table rental_contract (

eca_id int not null references entity_credit_account(id),

schedule_id int not null references schedule(id),

rental_id int not null references rental(id),

rental_period tsrange, -- should this be tstzrange?

auto_renew bool not null default false,

shipping_address int references location(id)

);

Best Wishes,

Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.

http://www.efficito.com/learn_more