Subversion Repositories computer_asset_manager_v1

drop table license;
create table license (
   license_id int unsigned not null auto_increment,
   client_id  int unsigned null comment 'link to client table',
   device_id   int unsigned null comment 'link to device table',
   license text not null comment 'the actual license text',
   license_product_id int unsigned not null references license_product(license_product_id),
   added_date date default null comment 'date the license used on this machine',
   removed_date date default null comment 'date license removed from this machine',
   primary key (license_id)
) comment 'Holds the license and links to machine installed on' ;
   
drop table license_product;
create table license_product (
   license_product_id int unsigned not null auto_increment,
   name text comment 'name of the license',
   primary key ( license_product_id )
) comment 'a list of all products we track license for';

select 
   license.license 'License',
   license_product.name 'Product',
   ifnull(device.name,'--- Available ---') 'Installed On'
from
   license join license_product using (license_product_id)
   left outer join device using (device_id)
order by license_product.name, device.name;