#Letter# #tickets# ## #This table stores all the tickets attended and finished COLUMNS: ticket_code: obtained from tickets_ticket_code_seq employee_id: NIF of the waiter who was paid for the ticket start_time: stamptime when the order was first input in the system end_time: stamptime when the ticket was paid pay_type: the way the costumer paid the ticket (cash, card...) ticket_state: the state for the ticket. Usually: paid CONSTRAINTS: if a ticket_code is updated it will: cascade to tables: ticket_table ticket_items if a ticket_code is deleted it will: cascade to tables: ticket_table ticket_items# #ticket_code# #integer# ## ## #employee_id# #varchar# ## ## #start_time# #timestamp with time zone# #NOT NULL# ## #end_time# #timestamp with time zone# #NOT NULL# ## #pay_type# #varchar# ## ## #ticket_state# #varchar# ## ## ## #ticket_code# ## #ticket_code# #ON UPDATE CASCADE ON DELETE CASCADE# #pay_types# ## #This table lists and describes ways of charging costumers. COLUMNS: pay_type: name of the paying type pay_fare: fare charged for the paying type. ie the bank charging for getting paid with a credit card. description: ejem... CONSTRAINTS: if a pay_type is updated it will: restrict if exists in tables: tickets if a pay_type is deleted it will: restrict if exists in tables: tickets# #pay_type# #varchar# ## ## #pay_fare# #numeric# ## ## #description# #varchar# ## ## ## #pay_type# ## #pay_type# #ON UPDATE CASCADE# #products# ## #This table lists all the existing products. COLUMNS: product_code: code name of the product. If it has more than one ingredient, the code is the catenation of the product_code of the ingredients, with a space as separator and alphanumerically sorted product: descriptive name for the product (will appear in the inteface). price: base price for the product (after taxes), prior to modifiers dued to options or offers. tax: basic tax for the prodcut. logo: filesystem path to a file with the logo of the product description: comments and stuff like that. CONSTRAINTS: if a product_code is updated it will: cascade to tables: init_prod_options init_prod_offers prod_composition restrict if exists in tables: ticket_items if a product_code is deleted it will: cascade to tables: init_prod_options init_prod_offers prod_composition restrict if exists in tables: ticket_items# #product_code# #varchar# ## ## #product# #varchar# #NOT NULL UNIQUE# ## #price# #numeric# #NOT NULL# ## #tax# #varchar# ## ## #logo# #varchar# ## ## #description# #varchar# ## ## ## #product_code# ## #product_code# #ON DELETE CASCADE ON UPDATE CASCADE# #taxes# ## #This table lists and describes the possible taxes to apply to products. COLUMNS: tax: name of the tax. rate: factor applied to product price for the tax description: . CONSTRAINTS: if a tax is updated it will: restrict if exists in tables: ticket_items if a tax is deleted it will: restrict if exists in tables: ticket_items# #tax# #varchar# ## ## #rate# #numeric# #NOT NULL# ## #description# #varchar# ## ## ## #tax# ## #tax# ## #goods# ## ## #good_code# #varchar# ## ## #good# #varchar# #NOT NULL UNIQUE# ## #price# #numeric# ## ## #length# #numeric# ## ## #width# #numeric# ## ## #height# #numeric# ## ## #barcode# #varchar# #UNIQUE# ## ## #product_code# ## #product_code# ## #init_prod_options# ## #This table is a relation many to many of products and options. COLUMNS: product_code: code name for a product. option_type: type of the product option prod_option: the option for the product. value: price modifier for the product (assigned) is_default: some options may be the default ones CONSTRAINTS: if a (option_type, prod_option) is updated it will: restrict if exists in tables: ticket_item_opts if a (option_type, prod_option) is deleted it will: restrict if exists in tables: ticket_item_opts # #product_code# #varchar# ## ## #option_type# #varchar# ## ## #prod_option# #varchar# ## ## #value# #varchar# ## ## #is_default# #boolean# ## ## ## #item_code# ## #item_code# #ON DELETE CASCADE ON UPDATE CASCADE# #postgres: pre# #create sequence tickets_ticket_code_seq;# #prod_composition# ## #This table lists all the ingredients of products (unitary ones are ingredients for themselves) COLUMNS: product_code: the ingredients are for this product ingredient_code: product_code of an ingredient of the product quantity: how much ingredient is used when preparing the product NOTE: There is an inconsistency in the table constraints. It could happen that an ingredient is deleted from the products table, but the composite product would still have entries in the system and there would be incoherent data. The database won't complain if this happens.# #product_code# #varchar# ## ## #ingredient_code# #varchar# ## ## #quantity# #numeric# #NOT NULL# ## #ordering# #integer# ## ## ## #product_code# ## #ingredient_code# #ON DELETE CASCADE ON UPDATE CASCADE# #ticket_table# ## #This table is a relation 1 to many of a ticket and the table where was attended It's not in table tickets for storage space and performance reasons COLUMNS: ticket_code: the ticket referenced lounge_code: the lounge the table is in table_code: the table in question # #ticket_code# #integer# ## ## #lounge_code# #varchar# ## ## #table_code# #varchar# ## ## #ticket_states# ## #This table lists and describes all the posible states a costumer,s ticket can have. COLUMNS: ticket_state: the name of the state description: ejem... CONSTRAINTS: if a ticket_state is updated it will: restrict if exists in tables: tickets if a ticket_code is deleted it will: restrict if exists in tables: tickets# #ticket_state# #varchar# ## ## #description# #varchar# ## ## ## #ticket_state# ## #ticket_state# #ON UPDATE CASCADE# ## #ticket_code# ## #ticket_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #employee_id# ## #employee_id# #ON DELETE CASCADE ON UPDATE CASCADE# #acl# ## #This is a relation many to many among user groups and permissions. The description shouldn't be there, but then... COLUMNS: usr_group: user group permission: name of permission description: what can I say? -- usr_group varchar, -- CONSTRAINT acl_usr_group_fkey FOREIGN KEY (usr_group) REFERENCES usr_groups(usr_group) ON DELETE CASCADE ON UPDATE CASCADE, -- PRIMARY KEY (usr_group, permission),# #permission# #varchar# ## ## #employee_id# #varchar# ## ## #description# #varchar# ## ## ## #employee_id# ## #employee_id# #ON DELETE CASCADE ON UPDATE CASCADE# #permissions# ## #This table lists and describes the possible permissions in the system. COLUMNS: permission: name of permission description: well... CONSTRAINTS: if a permission is updated it will: cascade to tables: acl if a permission is deleted it will: cascade to tables: acl # #permission# #varchar# ## ## #description# #varchar# ## ## ## #company_id# ## #company_id# #ON UPDATE CASCADE# ## #employee_id# ## #employee_id# #ON UPDATE CASCADE# ## #employee_id# ## #employee_id# #ON UPDATE CASCADE# #orders# ## #This table stores all the orders attended, but not finished COLUMNS: order_code: obtained from orders_order_code_seq table_code: the order should be assigned to a table or place in the bar order_state: the order can be, for instance, service pending, in kitchen, waiting for being paid... employee_id: NIF of the waiter responsible for the order start_time: stamptime when the order was first input in the system xml: for performance reasons, the xml code for this order in the core application order_code integer PRIMARY KEY,# #employee_id# #varchar# #not null# ## #start_time# #timestamp with time zone# #not null# ## #last_time# #timestamp with time zone# ## ## #table_code# #varchar# #unique# ## #description# #varchar# ## ## #order_state# #varchar# ## ## #blocked# #boolean# #not null# ## #blocker# #cidr# ## ## #xml# #varchar# #not null# ## #order_states# ## #This table lists and describes all the posible states a costumer's order can have. COLUMNS: order_state: the name of the state description: ejem... CONSTRAINTS: if a order_state is updated it will: restrict if exists in tables: orders if a order_code is deleted it will: restrict if exists in tables: orders # #order_state# #varchar# ## ## #description# #varchar# ## ## ## #order_state# ## #order_state# #ON UPDATE CASCADE# #company_phones# ## #This table contains information about the phones of a company, as requested by one of the system management tool developers COLUMNS: company_id: CIF phone: a company phone description: type of phone (info, support...)# #company_id# #varchar# ## ## #phone# #varchar# ## ## #description# #varchar# ## ## ## #company_id# ## #company_id# #ON DELETE CASCADE ON UPDATE CASCADE# #postgres: pre# #create sequence premises_premises_code_seq;# #postgres: pre# #create sequence premises_distribution_lounge_code_seq;# ## #premises_code# ## #premises_code# #ON UPDATE CASCADE# #postgres: pre# #create sequence lounge_distribution_table_code_seq;# ## #lounge_code# ## #lounge_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #ticket_code# ## #ticket_code# #ON UPDATE CASCADE# #pers_ticket_item_offers# ## #This table is the historic version of ticket_item_offers# #item_code# #integer# ## ## #offer_type# #varchar# ## ## #prod_offer# #varchar# ## ## #pers_ticket_table# ## #This table is the historic version of ticket_table# #ticket_code# #integer# ## ## #lounge_code# #varchar# ## ## #table_code# #varchar# ## ## ## #ticket_code# ## #ticket_code# #ON UPDATE CASCADE# #pers_event_log# ## #This table is just the same as event_log, but it stores historic event (further than a cash day) and doesn not have any constraint with event_types. CONSTRAINTS: if a event_code is updated it will: cascade to tables: pers_cash_movements if a event_code is deleted it will: cascade to tables: pers_cash_movements# #event_code# #integer# ## ## #employee_id# #varchar# #NOT NULL# ## #time_stamp# #timestamp with time zone# #NOT NULL# ## #event_type# #varchar# #NOT NULL# ## ## #event_code# ## #event_code# #ON UPDATE CASCADE ON DELETE CASCADE# #event_log# ## #This table stores all the events of interest that occur in the system. COLUMNS: event_code: obtained from event_log_event_code_seq employee_id: NIF of the employee related to the event time_stamp: moment of the event event_type: type of event that happened CONSTRAINTS: if a event_code is updated it will: cascade to tables: cash_movements if a event_code is deleted it will: cascade to tables: cash_movements# #event_code# #integer# ## ## #employee_id# #varchar# ## ## #time_stamp# #timestamp with time zone# #NOT NULL# ## #event_type# #varchar# #NOT NULL# ## ## #event_code# ## #event_code# #ON DELETE CASCADE ON UPDATE CASCADE# #postgres: pre# #create sequence event_log_event_code_seq;# #event_types# ## #This table lists all interesting events that can happen in the system. COLUMNS: event_type: the type of event description: guess :) # #event_type# #varchar# ## ## #description# #varchar# ## ## #invoices# ## ## #invoice_code# #integer# ## ## #moment# #timestamp with time zone# #not null# ## #ticket_code# #integer# #not null# ## #client_id# #varchar# ## ## #client_name# #varchar# ## ## #client_address# #varchar# ## ## #invoice_items# ## ## #item_code# #integer# ## ## #invoice_code# #integer# #NOT NULL# ## #product# #varchar# #NOT NULL# ## #price# #numeric# #NOT NULL# ## #units# #integer# #NOT NULL# ## #tax# #varchar# #NOT NULL# ## #tax_rate# #numeric# #NOT NULL# ## ## #invoice_code# ## #invoice_code# #ON UPDATE CASCADE# #invoice_item_opts# ## ## #item_code# #integer# ## ## #option_type# #varchar# ## ## #prod_option# #varchar# ## ## #postgres: pre# #create sequence invoices_invoice_code_seq;# #postgres: pre# #create sequence invoice_items_item_code_seq;# #postgres: pre# #create sequence ticket_items_item_code_seq;# ## #auth_type# ## #auth_type# #ON DELETE CASCADE ON UPDATE CASCADE# ## #employee_id# ## #employee_id# #ON DELETE CASCADE ON UPDATE CASCADE# #offers_list# ## #This table lists all possible options for option types for the products. COLUMNS: offer_type: type of the option (amazing, hmm?) prod_offer: name of the option description: guess CONSTRAINTS: if a (offer_type, prod_offer) is updated it will: cascade to tables: init_prod_offers if a (offer_type, prod_offer) is deleted it will: cascade to tables: init_prod_offers# #offer_type# #varchar# ## ## #prod_offer# #varchar# ## ## #logo# #varchar# ## ## #description# #varchar# ## ## #init_prod_offers# ## #This table is a relation many to many of products and offers. COLUMNS: product_code: code name for a product. offer_type: type of the product offer prod_offer: the offer for the product. value: price modifier for the product is_default: some offers may be the default ones CONSTRAINTS: if a (offer_type, prod_offer) is updated it will: restrict if exists in tables: ticket_item_offers if a (offer_type, prod_offer) is deleted it will: restrict if exists in tables: ticket_item_offers # #product_code# #varchar# ## ## #offer_type# #varchar# ## ## #prod_offer# #varchar# ## ## #value# #varchar# ## ## #is_default# #boolean# ## ## ## #product_code# ## #product_code# #ON DELETE CASCADE ON UPDATE CASCADE# #ticket_item_offers# ## #This table stores all the information about offers in ticket items. COLUMNS: item_code: the offers are for this item offer_type: the type of the offer applied to the item prod_offer: the product offer applied to the item # #item_code# #integer# ## ## #offer_type# #varchar# ## ## #prod_offer# #varchar# ## ## ## #item_code# ## #item_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #tax# ## #tax# ## #pers_ticket_item_opts# ## #This table is the historic version of ticket_item_opts# #item_code# #integer# ## ## #option_type# #varchar# ## ## #prod_option# #varchar# ## ## #invoice_item_offers# ## ## #item_code# #integer# ## ## #offer_type# #varchar# ## ## #prod_offer# #varchar# ## ## ## #product_code# ## #product_code# ## ## #good_code# ## #good_code# ## #provider_phones# ## ## #provider_id# #varchar# ## ## #phone_no# #varchar# ## ## ## #provider_id# ## #provider_id# #ON DELETE CASCADE ON UPDATE CASCADE# #goods_providers# ## ## #provider_id# #varchar# ## ## #good_code# #varchar# ## ## #price# #numeric# ## ## #description# #varchar# ## ## ## #provider_id# ## #provider_id# #ON DELETE CASCADE ON UPDATE CASCADE# ## #good_code# ## #good_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #warehouse_code# ## #warehouse_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #shelving_code# ## #shelving_code# #ON DELETE CASCADE ON UPDATE CASCADE# #shelf_distribution# ## ## #shelf_code# #varchar# ## ## #good_code# #varchar# ## ## #quantity# #numeric# ## ## ## #good_code# ## #good_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #shelf_code# ## #shelf_code# #ON UPDATE CASCADE# ## #good_code# ## #good_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #threshold_type# ## #threshold_type# #ON UPDATE CASCADE ON DELETE CASCADE# #postgres: pre# #create sequence delivery_notes_delivery_note_code_seq;# #delivery_notes# ## ## #delivery_note_code# #integer# ## ## #delivery_time# #timestamp with time zone# #NOT NULL# ## #provider_id# #varchar# ## ## #dst_warehouse_code# #varchar# ## ## #description# #varchar# ## ## ## #provider_id# ## #provider_id# #ON UPDATE CASCADE# ## #warehouse_code# ## #dst_warehouse_code# #ON UPDATE CASCADE# #delivery_note_items# ## ## #delivery_note_item_code# #integer# ## ## #delivery_note_code# #integer# ## ## #good_code# #varchar# #NOT NULL# ## #quantity# #integer# #NOT NULL# ## #price# #numeric# ## ## #description# #varchar# ## ## ## #delivery_note_code# ## #delivery_note_code# #ON DELETE CASCADE ON UPDATE CASCADE# #postgres: pre# #create sequence delivery_note_items_delivery_note_item_code_seq;# #postgres: pre# #create sequence warehouse_movements_warehouse_movement_code_seq;# #warehouse_movements# ## ## #warehouse_movement_code# #integer# ## ## #src_warehouse_code# #varchar# ## ## #movement_time# #timestamp with time zone# #NOT NULL# ## #delivery_note_code# #integer# ## ## ## #delivery_note_code# ## #delivery_note_code# #ON DELETE CASCADE ON UPDATE CASCADE# ## #warehouse_code# ## #src_warehouse_code# #ON UPDATE CASCADE# #postgres:pre# #SET client_encoding = 'UTF8'; SET check_function_bodies = false; SET client_min_messages = warning; COMMENT ON SCHEMA public IS 'Standard public schema'; SET search_path = public, pg_catalog;# ## #company_id# ## #company_id# #ON DELETE CASCADE ON UPDATE CASCADE# #premises_distribution# ## #This table contains information about the lounges in the premises COLUMNS: lounge_code: obtained from premises_distribution_lounge_code_seq premises_code: premises the lounge is in lounge: name of the lounge map: filesystem path to a file containing the map description: general info about the lounge CONSTRAINTS: if a lounge_code is updated it will: cascade to tables: lounge_distribution if a lounge_code is deleted it will: cascade to tables: lounge_distribution # #lounge_code# #integer# ## ## #premises_code# #varchar# ## ## #lounge# #varchar# #NOT NULL# ## #map# #varchar# ## ## #description# #varchar# ## ## #lounge_distribution# ## #This table contains information about the tables distribution in the lounges COLUMNS: table_code: obtained from lounge_distribution_table_code_seq lounge_code: the lounge the table is in table_name: name of the table. The field cannot be called table for obvious reasons. table_locus: x,y coordinates for table location in the lounge map description: general info about the table CONSTRAINTS: if a table_code is updated it will: cascade to tables: ticket_table orders if a table_code is deleted it will: cascade to tables: ____ # #table_code# #varchar# ## ## #lounge_code# #varchar# ## ## #premises_code# #varchar# ## ## #table_name# #varchar# ## ## #table_locus# #point# ## ## #description# #varchar# ## ## ## #premises_code# ## #premises_code# #ON DELETE CASCADE ON UPDATE CASCADE# #companies# ## #This table contains information about the company itself. When migrating to a distributed enviroment, it may happen that this table stores information about more than one company. COLUMNS: company_id: CIF company: name of the company address: company headquarters address contact: manager email: contact email address for the company (kind of info@company.com) fax: fax number for the company headquarters description: any comment goes here logo: filesystem path to an image file containing the logo of the company CONSTRAINTS: if a company_id is updated it will: cascade to tables: company_phones premises staff if a company_id is deleted it will: cascade to tables: company_phones restrict if exists in tables: premises staff# #company_id# #varchar# ## ## #company# #varchar# #NOT NULL UNIQUE# ## #address# #varchar# ## ## #contact# #varchar# ## ## #email# #varchar# ## ## #fax# #varchar# ## ## #description# #varchar# ## ## #logo# #varchar# ## ## #premises# ## #This table contains information about the premises of the companies COLUMNS: premises_code: obtained from premises_premises_code_seq premises: name of the premises phone: phone for that particular premises address: address for that particular premises company_id: CIF description: general info about the premises CONSTRAINTS: if a premises_code is updated it will: cascade to tables: premises_distribution premises_staff if a premises_code is deleted it will: cascade to tables: premises_distribution premises_staff NOTE: should it have a manager column, too??# #premises_code# #varchar# ## ## #premises# #varchar# ## ## #phone# #varchar# ## ## #address# #varchar# ## ## #company_id# #varchar# ## ## #description# #varchar# ## ## #premises_staff# ## #This table creates a many to many relation among employees and the premises they work in. COLUMNS: employee_id: NIF premises_code: premises where the employee works # #employee_id# #varchar# ## ## #premises_code# #varchar# ## ## ## #employee_id# ## #employee_id# #ON DELETE CASCADE ON UPDATE CASCADE# #competences# ## #This table is a relation many to many among employees and user groups. COLUMNS: employee_id: NIF usr_group: user group# #employee_id# #varchar# ## ## #usr_group# #varchar# ## ## ## #usr_group# ## #usr_group# #ON DELETE CASCADE ON UPDATE CASCADE# #authentication_types# ## #RTBS if will be used# #auth_type# #varchar# ## ## #description# #varchar# ## ## #authentication# ## #RTBS if will be used# #employee_id# #varchar# ## ## #code# #varchar# ## ## #auth_type# #varchar# ## ## ## #permission# ## #permission# #ON DELETE CASCADE ON UPDATE CASCADE# #usr_groups# ## #This table lists and describes the posible user groups that can exist in the system. COLUMNS: usr_group: group name. group is a reserved word, so it's not usable description: well... CONSTRAINTS: if a usr_group is updated it will: cascade to tables: competences acl if a usr_group is deleted it will: cascade to tables: competences acl # #usr_group# #varchar# ## ## #description# #varchar# ## ## #ticket_items# ## #This table lists the products sold in a ticket. COLUMNS: item_code: obtained from ticket_items_item_code_seq ticket_code: the item was for this ticket product_code: the product sold price: final unitary price for the product (after taxes) units: number of units sold of this product order_time: timestamp of the moment the item was last ordered tax: applied tax to product price tax_rate: for performance reasons. It should be the same as the one in the taxes table CONSTRAINTS: if a item_code is updated it will: cascade to tables: ticket_item_opts ticket_item_offers if a item_code is deleted it will: cascade to tables: ticket_item_opts ticket_item_offers# #item_code# #integer# ## ## #ticket_code# #integer# ## ## #product_code# #varchar# ## ## #price# #numeric# #NOT NULL# ## #units# #integer# #NOT NULL# ## #order_time# #timestamp with time zone# #NOT NULL# ## #tax# #varchar# ## ## #tax_rate# #numeric# #NOT NULL# ## #products_options_list# ## ## #option_type# #varchar# ## ## #prod_option# #varchar# ## ## #logo# #varchar# ## ## #description# #varchar# ## ## #option_types# ## #This table lists and describes the possible option types for the products COLUMN: option_type: no comment description: idem CONSTRAINTS: if a option_type is updated it will: cascade to tables: products_options_list if a option_type is deleted it will: cascade to tables: products_options_list # #option_type# #varchar# ## ## #description# #varchar# ## ## ## #offer_type# ## #offer_type# #ON DELETE CASCADE ON UPDATE CASCADE# ## #option_type, prod_option# ## #option_type, prod_option# #ON DELETE CASCADE ON UPDATE CASCADE# ## #offer_type, prod_offer# ## #offer_type, prod_offer# #ON DELETE CASCADE ON UPDATE CASCADE# ## #product_code# ## #product_code# #ON DELETE CASCADE ON UPDATE CASCADE# #staff# ## #This table contains information about the managed staff COLUMNS: employee_id: NIF name: given name last_name: well, well. address: postal address. nss: national health insurance number phone: not cellular cellular: well... the number, not the model ;o) email: no comment. company_id: CIF of the company that hired the employee services. picture: filesystem path to an employee's picture. active: boolean field allowing user to operate it defaults to true (false when deleted, so historical data can still be collected). CONSTRAINTS: if a employee_id is updated it will: cascade to tables: premises_staff competences restrict if exists in tables: orders tickets if a lounge_code is deleted it will: cascade to tables: premises_staff competences restrict if exists in tables: orders tickets# #employee_id# #varchar# ## ## #name# #varchar# #NOT NULL# ## #last_name# #varchar# ## ## #address# #varchar# ## ## #nss# #varchar# ## ## #phone# #varchar# ## ## #cellular# #varchar# ## ## #email# #varchar# ## ## #company_id# #varchar# ## ## #picture# #varchar# ## ## #active# #boolean# #default true not null# ## ## #option_type, prod_option# ## #option_type, prod_option# ## ## #offer_type, prod_offer# ## #offer_type, prod_offer# ## #offer_types# ## #This table lists and describes the possible offer types for the products COLUMN: offer_type: no comment cpp_operator: operator for applying offer value to product prices description: idem CONSTRAINTS: if a offer_type is updated it will: cascade to tables: offers_list if a offer_type is deleted it will: cascade to tables: offers_list# #offer_type# #varchar# ## ## #cpp_operator# #varchar# ## ## #description# #varchar# ## ## #ticket_item_opts# ## #This table stores all the information about options in ticket items. COLUMNS: item_code: the options are for this item option_type: the type of the option applied to the item prod_option: the product option applied to the item# #item_code# #integer# ## ## #option_type# #varchar# ## ## #prod_option# #varchar# ## ## ## #event_type# ## #event_type# #ON UPDATE CASCADE# #cash_movements# ## #This table stores all cash information about events COLUMNS: event_code: event related to the cash movement ticket_code: if the event was a ticket event, this will be the related ticket. It is bizarre, but, most of the cash events are ticket events, it's made to enhance performance. quantity: the quantity of money of the cash event# #event_code# #integer# ## ## #ticket_code# #integer# ## ## #quantity# #numeric# #NOT NULL# ## #pers_cash_movements_ticket_code# ## #index# ## #ticket_code# ## ## ## ## #item_code# ## #item_code# #ON UPDATE CASCADE# #pers_ticket_items# ## #This table is the historic version of ticket_items COLUMNS: One more column product: is the product name, so it can safely removed from the offered products, and still be available for statistics CONSTRAINTS: if a item_code is updated it will: cascade to tables: pers_ticket_opts pers_ticket_offers if a item_code is deleted it will: restrict if exists in tables: pers_ticket_opts pers_ticket_offers # #item_code# #integer# ## ## #ticket_code# #integer# ## ## #product_code# #varchar# ## ## #product# #varchar# ## ## #price# #numeric# #NOT NULL# ## #units# #integer# #NOT NULL# ## #tax_rate# #numeric# ## ## #tax# #varchar# ## ## #order_time# #timestamp with time zone# #NOT NULL# ## #pers_ticket_items_ticket_code# ## #index# ## #ticket_code# ## ## ## ## #item_code# ## #item_code# #ON UPDATE CASCADE# ## #item_code# ## #item_code# #ON UPDATE CASCADE# ## #item_code# ## #item_code# #ON UPDATE CASCADE# #good_unitary_product# ## ## #good_code# #varchar# ## ## #product_code# #varchar# #UNIQUE# ## #warehouses# ## ## #warehouse_code# #varchar# ## ## #warehouse# #varchar# #NOT NULL UNIQUE# ## #phone# #varchar# ## ## #address# #varchar# ## ## #description# #varchar# ## ## #warehouse_distribution# ## ## #shelving_code# #varchar# ## ## #warehouse_code# #varchar# ## ## #shelving# #varchar# ## ## #heights# #integer# ## ## #length# #integer# ## ## #shelving_locus# #point# ## ## #description# #varchar# ## ## #shelving_distribution# ## ## #shelf_code# #varchar# ## ## #warehouse_code# #varchar# ## ## #shelving_code# #varchar# ## ## #shelf# #varchar# ## ## #shelf_locus# #point# ## ## #description# #varchar# ## ## #barcode# #varchar# #UNIQUE# ## #threshold_types# ## ## #threshold_type# #varchar# ## ## #description# #varchar# ## ## #stock_thresholds# ## ## #good_code# #varchar# ## ## #threshold_type# #varchar# ## ## #quantity# #numeric# ## ## ## #good_code# ## #good_code# #ON UPDATE CASCADE# ## #table_code# ## #table_code# #ON UPDATE CASCADE# #providers# ## ## #provider_id# #varchar# ## ## #provider# #varchar# #NOT NULL UNIQUE# ## #address# #varchar# ## ## #phone# #varchar# ## ## #fax# #varchar# ## ## #email# #varchar# ## ## ## #option_type# ## #option_type# #ON DELETE CASCADE ON UPDATE CASCADE# #pers_cash_movements# ## #This table is the historic version of cash_movements. It keeps the constraint with pers_event_log# #event_code# #integer# ## ## #ticket_code# #integer# ## ## #quantity# #numeric# #NOT NULL# ## #pers_cash_movements_ticket_code# ## #index# ## #ticket_code# ## ## ## #pers_tickets# ## #This table is the historic version of tickets. COLUMNS: tickets and pers_tickets differ in a column: pay_fare: obtained from pay_types missing-> ticket_state: all tickets in this table have state: paid CONSTRAINTS: if a ticket_code is updated it will: cascade to tables: pers_ticket_table pers_ticket_items if a ticket_code is deleted it will: restrict if exists in tables: pers_ticket_table pers_ticket_items # #ticket_code# #integer# ## ## #employee_id# #varchar# #NOT NULL# ## #start_time# #timestamp with time zone# #NOT NULL# ## #end_time# #timestamp with time zone# #NOT NULL# ## #pay_type# #varchar# #NOT NULL# ## #pay_fare# #numeric# ## ##