Project

General

Profile

Simplfied Layout

1 components-db-simplified Table comments as mouse-over tooltips. DbSchema.com Fk fk_attributes_components attributes ref components ( component_id ) Fk fk_attributes_components attributes ref components ( component_id ) component_id Fk fk_attributes_type_id_status_id attributes ref statuses ( component_type_id ) Fk fk_attributes_type_id_status_id attributes ref statuses ( component_type_id ) component_type_id Fk fk_batches_purchase_orders batches ref purchase_orders ( purchase_order_id ) Fk fk_batches_purchase_orders batches ref purchase_orders ( purchase_order_id ) purchase_order_id Fk fk_batches_geographic_locations batches ref geographic_locations ( geo_loc_id ) Fk fk_batches_geographic_locations batches ref geographic_locations ( geo_loc_id ) geo_loc_id Fk fk_cable_structures_components cable_structures ref components ( cable_id -> component_id ) Fk fk_cable_structures_components cable_structures ref components ( cable_id -> component_id ) cable_id Fk fk_cable_structures_components_0 cable_structures ref components ( connection_a -> component_id ) Fk fk_cable_structures_components_0 cable_structures ref components ( connection_a -> component_id ) connection_a Fk fk_cable_structures_components_1 cable_structures ref components ( connection_b -> component_id ) Fk fk_cable_structures_components_1 cable_structures ref components ( connection_b -> component_id ) connection_b Fk fk_cable_structures_geographic_locations cable_structures ref geographic_locations ( geo_loc_id ) Fk fk_cable_structures_geographic_locations cable_structures ref geographic_locations ( geo_loc_id ) geo_loc_id Fk fk_component_tests_components component_tests ref components ( component_id ) Fk fk_component_tests_components component_tests ref components ( component_id ) component_id Fk fk_component_tests_component_test_types component_tests ref component_test_types ( comp_test_type_id ) Fk fk_component_tests_component_test_types component_tests ref component_test_types ( comp_test_type_id ) comp_test_type_id Fk fk_component_types_manufacturers_component_types component_types_manufacturers ref component_types ( component_type_id ) Fk fk_component_types_manufacturers_component_types component_types_manufacturers ref component_types ( component_type_id ) component_type_id Fk fk_component_types_manufacturers_manufacturers component_types_manufacturers ref manufacturers ( manufacturer_id ) Fk fk_component_types_manufacturers_manufacturers component_types_manufacturers ref manufacturers ( manufacturer_id ) manufacturer_id Fk fk_components_component_types components ref component_types ( component_type_id ) Fk fk_components_component_types components ref component_types ( component_type_id ) component_type_id Fk fk_components_batches components ref batches ( batch_id ) Fk fk_components_batches components ref batches ( batch_id ) batch_id Fk fk_components_manufacturers components ref manufacturers ( manufacturer_id ) Fk fk_components_manufacturers components ref manufacturers ( manufacturer_id ) manufacturer_id Fk fk_images_components images ref components ( component_id ) Fk fk_images_components images ref components ( component_id ) component_id Fk fk_statuses_component_types statuses ref component_types ( component_type_id ) Fk fk_statuses_component_types statuses ref component_types ( component_type_id ) component_type_id Fk fk_structures_components structures ref components ( component_id ) Fk fk_structures_components structures ref components ( component_id ) component_id Fk fk_structures_geographic_locations structures ref geographic_locations ( geo_loc_id ) Fk fk_structures_geographic_locations structures ref geographic_locations ( geo_loc_id ) geo_loc_id attributesTable public.attributes Pk idx_attributes ( component_id, created ) component_idcomponent_id * integer References components ( component_id ) Pk idx_attributes ( component_id, created ) createdcreated * timestamptz default CURRENT_TIMESTAMP d creator_idcreator_id * integer References users ( creator_id -> userid ) namename * text t propertiesproperties * jsonb commentscomments text t status_idstatus_id integer # component_type_idcomponent_type_id integer References statuses ( component_type_id ) batchesTable public.batches Pk pk_batches_batch_id ( batch_id ) batch_idbatch_id * integer Referred by components ( batch_id ) purchase_order_idpurchase_order_id integer References purchase_orders ( purchase_order_id ) received_datereceived_date date d reciever_idreciever_id integer References users ( reciever_id -> userid ) quantity_recievedquantity_recieved integer # geo_loc_idgeo_loc_id integer References geographic_locations ( geo_loc_id ) creator_idcreator_id * integer References users ( creator_id -> userid ) References users ( creator_id -> userid ) createdcreated * timestamptz default CURRENT_TIMESTAMP d cable_structuresTable public.cable_structures Pk pk_cable_structures_component_id ( cable_id, created ) cable_idcable_id * integer References components ( cable_id -> component_id ) Pk pk_cable_structures_component_id ( cable_id, created ) createdcreated * timestamptz default CURRENT_TIMESTAMP d creator_idcreator_id * integer References users ( creator_id -> userid ) operationoperation * text t commentscomments text t geo_loc_idgeo_loc_id integer References geographic_locations ( geo_loc_id ) connection_aconnection_a integer References components ( connection_a -> component_id ) connection_bconnection_b integer References components ( connection_b -> component_id ) component_test_typesTable public.component_test_types Pk pk_component_test_types_comp_test_type_id ( comp_test_type_id ) comp_test_type_idcomp_test_type_id * integer Referred by component_tests ( comp_test_type_id ) namename text t createdcreated * timestamptz default CURRENT_TIMESTAMP d creator_idcreator_id * integer References users ( creator_id -> userid ) commentscomments text t propertiesproperties jsonb component_testsTable public.component_tests Pk pk_component_tests_component_id ( component_id, comp_test_type_id, created ) component_idcomponent_id * integer References components ( component_id ) Pk pk_component_tests_component_id ( component_id, comp_test_type_id, created ) comp_test_type_idcomp_test_type_id * integer References component_test_types ( comp_test_type_id ) Pk pk_component_tests_component_id ( component_id, comp_test_type_id, created ) createdcreated * timestamptz default CURRENT_TIMESTAMP d creator_idcreator_id * integer References users ( creator_id -> userid ) test_datatest_data * jsonb commentscomments text t component_typesTable public.component_types Pk pk_component_types_component_type_id ( component_type_id ) component_type_idcomponent_type_id * integer Referred by component_types_manufacturers ( component_type_id ) Referred by component_types_roles ( component_type_id ) Referred by components ( component_type_id ) Referred by statuses ( component_type_id ) namename text t propertiesproperties jsonb commentscomments text t creator_idcreator_id * integer References users ( creator_id -> userid ) createdcreated * timestamptz default CURRENT_TIMESTAMP d component_types_manufacturersTable public.component_types_manufacturers Pk pk_component_types_manufacturers_component_types ( component_type_id, manufacturer_id ) component_type_idcomponent_type_id * integer References component_types ( component_type_id ) Pk pk_component_types_manufacturers_component_types ( component_type_id, manufacturer_id ) manufacturer_idmanufacturer_id * integer References manufacturers ( manufacturer_id ) componentsTable public.components Pk pk_components_component_id ( component_id ) component_idcomponent_id * integer Referred by attributes ( component_id ) Referred by cable_structures ( cable_id -> component_id ) Referred by cable_structures ( connection_a -> component_id ) Referred by cable_structures ( connection_b -> component_id ) Referred by component_tests ( component_id ) Referred by images ( component_id ) Referred by structures ( component_id ) component_type_idcomponent_type_id * integer References component_types ( component_type_id ) Unq idx_components ( external_id ) external_idexternal_id * text t creator_idcreator_id * integer References users ( creator_id -> userid ) createdcreated * timestamptz default CURRENT_TIMESTAMP d batch_idbatch_id integer References batches ( batch_id ) serial_numberserial_number text t manufacturer_idmanufacturer_id integer References manufacturers ( manufacturer_id ) geographic_locationsTable public.geographic_locations Pk pk_geographic_locations_geo_loc_id ( geo_loc_id ) geo_loc_idgeo_loc_id * integer Referred by batches ( geo_loc_id ) Referred by cable_structures ( geo_loc_id ) Referred by structures ( geo_loc_id ) location_namelocation_name * text t imagesTable public.images Pk pk_images_image_id ( image_id ) image_idimage_id * integer # component_idcomponent_id * integer References components ( component_id ) imageimage * bytea ~ creator_idcreator_id * integer References users ( creator_id -> userid ) createdcreated * timestamptz default CURRENT_TIMESTAMP d commentscomments text t image_nameimage_name * text t manufacturersTable public.manufacturers Pk pk_manufacturers_manufacturer_id ( manufacturer_id ) manufacturer_idmanufacturer_id * integer Referred by component_types_manufacturers ( manufacturer_id ) Referred by components ( manufacturer_id ) namename text t purchase_ordersTable public.purchase_orders Pk pk_purchase_orders_purchase_order_id ( purchase_order_id ) purchase_order_idpurchase_order_id * integer Referred by batches ( purchase_order_id ) order_dateorder_date * date d po_numberpo_number text t creator_idcreator_id * integer References users ( creator_id -> userid ) createdcreated * timestamptz default CURRENT_TIMESTAMP d date_expecteddate_expected date d ordered_by_idordered_by_id integer References users ( ordered_by_id -> userid ) quantityquantity integer # statusesTable public.statuses Pk pk_statuses_status_id ( status_id ) status_idstatus_id * integer # Unq statuses_component_type_id_status_idx ( component_type_id, status ) component_type_idcomponent_type_id * integer References component_types ( component_type_id ) Unq statuses_component_type_id_status_idx ( component_type_id, status ) statusstatus * text t descriptiondescription text t creator_idcreator_id * integer References users ( creator_id -> userid ) createdcreated * timestamptz default CURRENT_TIMESTAMP d structuresTable public.structures Pk pk_structures_component_id ( component_id, created ) component_idcomponent_id * integer References components ( component_id ) Pk pk_structures_component_id ( component_id, created ) createdcreated * timestamptz default CURRENT_TIMESTAMP d creator_idcreator_id * integer References users ( creator_id -> userid ) structures_container_id_idx ( container_id ) container_idcontainer_id integer # functional_positionfunctional_position text t geo_loc_idgeo_loc_id integer References geographic_locations ( geo_loc_id ) operationoperation * text t commentscomments text t


Table attributes
IdxField NameData TypeDescription
* component_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
* creator_id integer
* name text
* properties jsonb
  comments text
  status_id integer
component_type_id integer
Indexes
idx_attributes ON component_id, created
Foreign Keys
fk_attributes_components ( component_id ) ref components (component_id)
fk_attributes_users ( creator_id ) ref users (userid)
fk_attributes_type_id_status_id ( component_type_id ) ref statuses (component_type_id)


Table batches
IdxField NameData TypeDescription
* batch_id integer
purchase_order_id integer
  received_date date
reciever_id integer
  quantity_recieved integer
geo_loc_id integer
* creator_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
Indexes
pk_batches_batch_id ON batch_id
Foreign Keys
fk_batches_users ( creator_id ) ref users (userid)
fk_batches_users ( creator_id ) ref users (userid)
fk_batches_users_1 ( reciever_id ) ref users (userid)
fk_batches_purchase_orders ( purchase_order_id ) ref purchase_orders (purchase_order_id)
fk_batches_geographic_locations ( geo_loc_id ) ref geographic_locations (geo_loc_id)


Table cable_structures
IdxField NameData TypeDescription
* cable_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
* creator_id integer
* operation text
  comments text
geo_loc_id integer
connection_a integer
connection_b integer
Indexes
pk_cable_structures_component_id ON cable_id, created
Foreign Keys
fk_cable_structures_components ( cable_id ) ref components (component_id)
fk_cable_structures_components_0 ( connection_a ) ref components (component_id)
fk_cable_structures_components_1 ( connection_b ) ref components (component_id)
fk_cable_structures_users ( creator_id ) ref users (userid)
fk_cable_structures_geographic_locations ( geo_loc_id ) ref geographic_locations (geo_loc_id)


Table component_test_types
IdxField NameData TypeDescription
* comp_test_type_id integer
  name text
* created timestamptz DEFAULT CURRENT_TIMESTAMP
* creator_id integer
  comments text
  properties jsonb
Indexes
pk_component_test_types_comp_test_type_id ON comp_test_type_id
Foreign Keys
component_test_types_creator_id_fkey ( creator_id ) ref users (userid)


Table component_tests
IdxField NameData TypeDescription
* component_id integer
* comp_test_type_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
* creator_id integer
* test_data jsonb
  comments text
Indexes
pk_component_tests_component_id ON component_id, comp_test_type_id, created
Foreign Keys
fk_component_tests_components ( component_id ) ref components (component_id)
fk_component_tests_users ( creator_id ) ref users (userid)
fk_component_tests_component_test_types ( comp_test_type_id ) ref component_test_types (comp_test_type_id)


Table component_types
IdxField NameData TypeDescription
* component_type_id integer
  name text
  properties jsonb
  comments text
* creator_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
Indexes
pk_component_types_component_type_id ON component_type_id
Foreign Keys
fk_component_types_users ( creator_id ) ref users (userid)


Table component_types_manufacturers
IdxField NameData TypeDescription
* component_type_id integer
* manufacturer_id integer
Indexes
pk_component_types_manufacturers_component_types ON component_type_id, manufacturer_id
Foreign Keys
fk_component_types_manufacturers_component_types ( component_type_id ) ref component_types (component_type_id)
fk_component_types_manufacturers_manufacturers ( manufacturer_id ) ref manufacturers (manufacturer_id)


Table components
IdxField NameData TypeDescription
* component_id integer
* component_type_id integer
* external_id text
* creator_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
batch_id integer
  serial_number text
manufacturer_id integer
Indexes
pk_components_component_id ON component_id
idx_components ON external_id
Foreign Keys
fk_components_component_types ( component_type_id ) ref component_types (component_type_id)
fk_components_users ( creator_id ) ref users (userid)
fk_components_batches ( batch_id ) ref batches (batch_id)
fk_components_manufacturers ( manufacturer_id ) ref manufacturers (manufacturer_id)


Table geographic_locations
IdxField NameData TypeDescription
* geo_loc_id integer
* location_name text
Indexes
pk_geographic_locations_geo_loc_id ON geo_loc_id


Table images
IdxField NameData TypeDescription
* image_id integer
* component_id integer
* image bytea
* creator_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
  comments text
* image_name text
Indexes
pk_images_image_id ON image_id
Foreign Keys
fk_images_components ( component_id ) ref components (component_id)
fk_images_users ( creator_id ) ref users (userid)


Table manufacturers
IdxField NameData TypeDescription
* manufacturer_id integer
  name text
Indexes
pk_manufacturers_manufacturer_id ON manufacturer_id


Table purchase_orders
IdxField NameData TypeDescription
* purchase_order_id integer
* order_date date
  po_number text
* creator_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
  date_expected date
ordered_by_id integer
  quantity integer
Indexes
pk_purchase_orders_purchase_order_id ON purchase_order_id
Foreign Keys
fk_purchase_orders_users ( creator_id ) ref users (userid)
fk_purchase_orders_users_0 ( ordered_by_id ) ref users (userid)


Table statuses
IdxField NameData TypeDescription
* status_id integer
* component_type_id integer
* status text
  description text
* creator_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
Indexes
pk_statuses_status_id ON status_id
statuses_component_type_id_status_idx ON component_type_id, status
Foreign Keys
fk_statuses_component_types ( component_type_id ) ref component_types (component_type_id)
fk_statuses_users ( creator_id ) ref users (userid)


Table structures
IdxField NameData TypeDescription
* component_id integer
* created timestamptz DEFAULT CURRENT_TIMESTAMP
* creator_id integer
container_id integer
  functional_position text
geo_loc_id integer
* operation text
  comments text
Indexes
pk_structures_component_id ON component_id, created
structures_container_id_idx ON container_id
Foreign Keys
fk_structures_components ( component_id ) ref components (component_id)
fk_structures_users ( creator_id ) ref users (userid)
fk_structures_geographic_locations ( geo_loc_id ) ref geographic_locations (geo_loc_id)