Last active
January 7, 2021 04:24
-
-
Save jaycverg/a09f88ccf19232bce37ddfed646b6c4d to your computer and use it in GitHub Desktop.
DB structure for a generic warehouse/inventory system
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # db structure from https://stackoverflow.com/a/37607350/896212 | |
| Sites | |
| - id | |
| - site_code | |
| - site_name | |
| Warehouse | |
| - id | |
| - site_id | |
| - warehouse_code | |
| - warehouse_name | |
| Item Category | |
| - id | |
| - category_code | |
| - category_name | |
| Item Group | |
| - id | |
| - group_code | |
| - group_name | |
| Generic Product | |
| - id | |
| - generic_name | |
| Product | |
| - id | |
| - product_code | |
| - category_id | |
| - group_id | |
| - brand_id | |
| - generic_id | |
| - model_id/part_id | |
| - product_name | |
| - product_description | |
| - product_price (current rate) | |
| - has_instances(y/n) | |
| - has_lots (y/n) | |
| - has_attributes | |
| - default_uom | |
| - pack_size | |
| - average_cost | |
| - single_unit_product_code (for packs) | |
| - dimension_group (pointing to dimensions) | |
| - lot_information | |
| - warranty_terms (general not specific) | |
| - is_active | |
| - deleted | |
| Product attribute type (color/size etc.) | |
| - id | |
| - attribute_name | |
| Product_attribute | |
| - id | |
| - product_id | |
| - attribute_id | |
| Product attribute value (this product -> red) | |
| - id | |
| - product_attribute_id | |
| - value | |
| Product_instance | |
| - id | |
| - product_id | |
| - instance_name (as given by manufacturer) | |
| - serial_number | |
| - brand_id (is this brand) | |
| - stock_id (stock record pointing qih, location etc.) | |
| - lot_information (lot_id) | |
| - warranty_terms | |
| - product attribute value id (if applicable) | |
| Product lot | |
| - id | |
| - lot_code/batch_code | |
| - date_manufactured | |
| - date_expiry | |
| - product attribute value id (if applicable) | |
| Brand | |
| - id | |
| - manufacturer_id | |
| - brand_code | |
| - brand_name | |
| Brand Manufacturer | |
| - id | |
| - manufacturer_name | |
| Stock | |
| - id | |
| - product_id | |
| - warehouse_id, zone_id, level_id, rack_id etc. | |
| - quantity in hand | |
| - product attribute value id (if applicable) [we have 4 red color items etc.] | |
| Product Price Records | |
| - product_id | |
| - from_date | |
| - product_price | |
| Purchase Order Header | |
| - id | |
| - supplier_id | |
| - purchase_date | |
| - total_amount | |
| Purchase Order Line | |
| - id | |
| - po_id | |
| - product_id | |
| - unit_price | |
| - quantity | |
| Supplier | |
| - id | |
| - supplier_code | |
| - supplier_name | |
| - supplier_type | |
| Product_uom | |
| - id | |
| - uom_name | |
| Product_uom_conversion | |
| - id | |
| - from_uom_id | |
| - to_uom_id | |
| - conversion_rule |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment