Data Structures for KPM and database relations

Models

• Address

  • DROP TABLE IF EXISTS public.address;
    
    CREATE TABLE public.address
    (
        uid SERIAL PRIMARY KEY,
        country CHAR(2) NOT NULL,              -- ISO 3166-1 alpha-2 (e.g., 'CH')
       region VARCHAR(100),                   -- State or canton
       city VARCHAR(100) NOT NULL,
        postcode VARCHAR(20) NOT NULL,
        street VARCHAR(255) NOT NULL,
       number VARCHAR(20),   
        unit VARCHAR(50),                      -- Apartment/Suite/Floor/etc.
       extra_address VARCHAR(255),            -- Additional info
       lon DOUBLE PRECISION,                  -- Longitude (optional)
       lat DOUBLE PRECISION                   -- Latitude (optional)
    )
    
    TABLESPACE pg_default;
    
    ALTER TABLE IF EXISTS public.address
       OWNER to kpm_rw;
    
    COMMENT ON TABLE public.address
       IS 'addresses table, instead of making use of addresses for ever company, customer and co... Prefer to make addresses table and then assign it to a company or a user etc.';

• Company_Type

  • -- Table: public.company_type
    
    -- DROP TABLE IF EXISTS public.company_type;
    
    CREATE TABLE IF NOT EXISTS public.company_type
    (
        uid SERIAL PRIMARY KEY,
        name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
        description VARCHAR(255) COLLATE pg_catalog."default",
        creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    TABLESPACE pg_default;
    
    ALTER TABLE IF EXISTS public.company_type
       OWNER TO kpm_rw;
    
    COMMENT ON TABLE public.company_type
       IS 'Reference table for different types of companies (e.g., manufacturer, distributor, partner, etc.). Used by company table.';
    
    COMMENT ON COLUMN public.company_type.uid
       IS 'Unique ID for each company type';
    
    COMMENT ON COLUMN public.company_type.name
       IS 'Short name of the company type (e.g., Manufacturer, EMS, Partner)';
    
    COMMENT ON COLUMN public.company_type.description
       IS 'Optional longer description of the company type';
    
    COMMENT ON COLUMN public.company_type.creation_date
       IS 'Timestamp when the entry was created';
    
    COMMENT ON COLUMN public.company_type.updated_date
       IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';

• Company_status

  • -- Table: public.company_status
    
    -- DROP TABLE IF EXISTS public.company_status;
    
    CREATE TABLE IF NOT EXISTS public.company_status
    (
        uid SERIAL PRIMARY KEY,
        name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
        description VARCHAR(255) COLLATE pg_catalog."default",
        creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    TABLESPACE pg_default;
    
    ALTER TABLE IF EXISTS public.company_status
       OWNER TO kpm_rw;
    
    COMMENT ON TABLE public.company_status
       IS 'Reference table for the status or lifecycle stage of a company (e.g., Active, Inactive, Prospect). Used by the company table.';
    
    COMMENT ON COLUMN public.company_status.uid
       IS 'Unique ID for each company status entry';
    
    COMMENT ON COLUMN public.company_status.name
       IS 'Short name of the status (e.g., Active, Archived, Lead)';
    
    COMMENT ON COLUMN public.company_status.description
       IS 'Optional description for the company status';
    
    COMMENT ON COLUMN public.company_status.creation_date
       IS 'Timestamp when the entry was created';
    
    COMMENT ON COLUMN public.company_status.updated_date
       IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';

  ---

  Company

  • Relation

    *

  • -- Table: public.company_relation
    
    -- DROP TABLE IF EXISTS public.company_relation;
    
    CREATE TABLE IF NOT EXISTS public.company_relation
    (
        uid SERIAL PRIMARY KEY,
        name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
        description VARCHAR(255) COLLATE pg_catalog."default",
        creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    TABLESPACE pg_default;
    
    ALTER TABLE IF EXISTS public.company_relation
       OWNER TO kpm_rw;
    
    COMMENT ON TABLE public.company_relation
       IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.';
    
    COMMENT ON COLUMN public.company_relation.uid
       IS 'Unique ID for each company relation type';
    
    COMMENT ON COLUMN public.company_relation.name
       IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)';
    
    COMMENT ON COLUMN public.company_relation.description
       IS 'Optional longer description of the relation type';
    
    COMMENT ON COLUMN public.company_relation.creation_date
       IS 'Timestamp when the entry was created';
    
    COMMENT ON COLUMN public.company_relation.updated_date
       IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';

  • Legal Form

  • -- Table: public.company_legal_form
    
    -- DROP TABLE IF EXISTS public.company_legal_form;
    
    CREATE TABLE IF NOT EXISTS public.company_legal_form
    (
        uid SERIAL PRIMARY KEY,
        name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
        description VARCHAR(255) COLLATE pg_catalog."default",
        creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    TABLESPACE pg_default;
    
    ALTER TABLE IF EXISTS public.company_legal_form
       OWNER TO kpm_rw;
    
    COMMENT ON TABLE public.company_legal_form
       IS 'Reference table for legal structures of companies (e.g., AG, GmbH, Ltd, Inc). Used by the company table.';
    
    COMMENT ON COLUMN public.company_legal_form.uid
       IS 'Unique ID for each legal form';
    
    COMMENT ON COLUMN public.company_legal_form.name
       IS 'Short name of the legal form (e.g., GmbH, AG, Ltd, Inc)';
    
    COMMENT ON COLUMN public.company_legal_form.description
       IS 'Optional longer description of the legal form';
    
    COMMENT ON COLUMN public.company_legal_form.creation_date
       IS 'Timestamp when the entry was created';
    
    COMMENT ON COLUMN public.company_legal_form.updated_date
       IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';