SF.net SVN: ledger-smb: [1247] trunk/sql/Pg-database.sql

[..hidden..]

Revision: 1247
          http://svn.sourceforge.net/ledger-smb/?rev=1247&view=rev
Author:   linuxpoet
Date:     2007-05-31 18:25:09 -0700 (Thu, 31 May 2007)

Log Message:
-----------
fix for authoritative addresses by adding location_class

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2007-05-29 22:48:30 UTC (rev 1246)
+++ trunk/sql/Pg-database.sql	2007-06-01 01:25:09 UTC (rev 1247)
@@ -55,8 +55,23 @@
 
 CREATE UNIQUE INDEX country_name_idx on country(lower(name));
 
+CREATE TABLE location_class (
+  id serial PRIMARY KEY,
+  class text check (class ~ '[[:alnum:]_]') not null,
+  authoritative boolean not null);
+  
+CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class));
+
+INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE);
+INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE);
+INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE);
+
+SELECT SETVAL('location_class_id_seq',4);
+  
+
 CREATE TABLE location (
   id serial PRIMARY KEY,
+  location_class integer not null references location(id),
   line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
   line_two text,
   line_three text,
@@ -68,12 +83,9 @@
   id serial UNIQUE,
   entity_id integer not null references entity(id),
   legal_name text check (legal_name ~ '[[:alnum:]_]'),
-  entity_class_id integer not null references entity_class(id),
-  primary_location_id integer references location(id),
   tax_id text,
-  PRIMARY KEY (legal_name,primary_location_id));
+  PRIMARY KEY (entity_id,legal_name));
   
-COMMENT ON COLUMN company.primary_location_id IS $$ This is the location that should show up by default for any forms $$;
 COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;  
 
 CREATE TABLE company_to_location (
@@ -101,11 +113,16 @@
  salutation_id integer references salutation(id),
  first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
  middle_name text,
- last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
- primary_location_id integer references location(id));
+ last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL
+ );
  
 COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$;
 
+CREATE TABLE person_to_location (
+  location_id integer not null references location(id),
+  person_id integer not null references person(id),
+  PRIMARY KEY (location_id,person_id));
+
 CREATE TABLE person_to_company (
   location_id integer references location(id) not null,
   person_id integer references person(id) not null,


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.