-- drop the old tables from the db
DROP TABLE building_defs;
DROP TABLE building_resources;
DROP TABLE resource_defs;
DROP TABLE building_bonuses;

-- statistics for the buildings
CREATE TABLE building_defs (
	build_id 	int,			-- identifier for the building (primary key)
	name 	varchar(80),		-- name of the building
	const_cost	int,			-- cost in credits for constructing the building
	const_units int,			-- cost in construction units for construction
	power_cost	int,			-- required power for the building
	tech_lvl	int				-- technical level of the building
);

-- association of buildings to resources produced
CREATE TABLE building_resources (
	build_id	int,			-- id of building in question (foreign key)
	res_id 		int,			-- id of resource in produced or consumed (foreign key)
	quantity	int				-- quantity of resource produced or consumed
);

-- association of building bonuses to colony stats
CREATE TABLE building_bonuses (
	build_id 	int,
	stat_id		int,
	value		int
);

-- statistics for the resources
CREATE TABLE resource_defs (
	res_id 		int,			-- id of the resource (primary key)
	name		varchar(80),	-- name of the resource
	descript 	varchar(100)	-- a description of the resource
);