GM,
Thank you for taking the time to look at this. Here's table structure
Code: Select all
CREATE TABLE bureau
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR NOT NULL
) WITHOUT OIDS;
INSERT INTO bureau VALUES( 1, 'Experian' );
INSERT INTO bureau VALUES( 2, 'Equifax' );
INSERT INTO bureau VALUES( 3, 'Transunion' );
Code: Select all
CREATE TABLE client
(
id SERIAL NOT NULL PRIMARY KEY,
entity_id_fk INT NOT NULL REFERENCES entity( "id" ),
agent_id_fk INT NOT NULL REFERENCES agent("id"),
partner_user_id_fk INT NOT NULL REFERENCES partner_user("id"),
author_id_fk INT NOT NULL REFERENCES entity( "id" ),
type_id_fk INT NOT NULL REFERENCES client_type( "id" ),
active BOOLEAN NOT NULL DEFAULT 't',
name VARCHAR NOT NULL,
email VARCHAR NULL,
password VARCHAR NULL,
street1 VARCHAR NULL,
street2 VARCHAR NULL,
city VARCHAR NULL,
state CHAR(2) NULL,
zipcode CHAR(10) NULL,
home_phone CHAR(15) NULL,
work_phone CHAR(15) NULL,
cell_phone CHAR(15) NULL,
datetime TIMESTAMP NOT NULL DEFAULT NOW(),
UNIQUE( id, entity_id_fk, email )
) WITHOUT OIDS;
INSERT INTO client VALUES (3, 68, 1, 1, 1, 1, true, 'Jon Doe III', 'jd@client.com', 'password', '123 Some Street', 'Suite 432', 'Phoenix', 'AZ', '85258 \
', '602-555-4567 ', '623-667-8989 ', '480-999-8789 ', '2006-08-31 17:12:25.188714');
INSERT INTO client VALUES (6, 70, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:48:58.920275');
INSERT INTO client VALUES (7, 71, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:11.787509');
INSERT INTO client VALUES (8, 72, 2, 1, 1, 1, true, 'James Jones', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:29.864063');
INSERT INTO client VALUES (9, 73, 2, 1, 1, 1, true, 'Steve Nash', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:35.074011');
INSERT INTO client VALUES (11, 75, 2, 3, 1, 1, true, 'Charles Barkley', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:51:02.103744');
INSERT INTO client VALUES (10, 74, 2, 3, 1, 1, true, 'Shaquil O\\''Neal', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:49.823933');
INSERT INTO client VALUES (4, 32, 1, 3, 1, 1, true, 'New Client', 'newclient@client.com', 'client', '123 Some Street', 'Suite 400', 'Phoenix', 'AZ', '85015 \
', '480-555-5555 ', '602-555-5555 ', '623-555-5555 ', '2006-09-05 08:51:02.629335');
INSERT INTO client VALUES (12, 87, 2, 1, 1, 1, true, 'Client Name', '', '', '', '', '', '-1', ' ', ' ', ' ', ' \
', '2006-09-18 12:23:49.57459');
and finally
Code: Select all
CREATE TABLE client_score
(
id SERIAL NOT NULL PRIMARY KEY,
client_id_fk INT NOT NULL REFERENCES client("id"),
bureau_id_fk INT NOT NULL REFERENCES bureau("id"),
start_score CREDITSCORE NOT NULL,
start_date TIMESTAMP NOT NULL DEFAULT NOW(),
update_score CREDITSCORE NOT NULL,
update_date TIMESTAMP NOT NULL DEFAULT NOW(),
target_score CREDITSCORE NOT NULL
) WITHOUT OIDS;
INSERT INTO client VALUES (3, 68, 1, 1, 1, 1, true, 'Jon Doe III', 'jd@client.com', 'password', '123 Some Street', 'Suite 432', 'Phoenix', 'AZ', '85258 \
', '602-555-4567 ', '623-667-8989 ', '480-999-8789 ', '2006-08-31 17:12:25.188714');
INSERT INTO client VALUES (6, 70, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:48:58.920275');
INSERT INTO client VALUES (7, 71, 2, 1, 1, 1, true, 'charles', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:11.787509');
INSERT INTO client VALUES (8, 72, 2, 1, 1, 1, true, 'James Jones', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:29.864063');
INSERT INTO client VALUES (9, 73, 2, 1, 1, 1, true, 'Steve Nash', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:35.074011');
INSERT INTO client VALUES (11, 75, 2, 3, 1, 1, true, 'Charles Barkley', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:51:02.103744');
INSERT INTO client VALUES (10, 74, 2, 3, 1, 1, true, 'Shaquil O\\''Neal', '', '', '', '', '', 'AL', ' ', ' ', ' ', ' \
', '2006-09-11 11:50:49.823933');
INSERT INTO client VALUES (4, 32, 1, 3, 1, 1, true, 'New Client', 'newclient@client.com', 'client', '123 Some Street', 'Suite 400', 'Phoenix', 'AZ', '85015 \
', '480-555-5555 ', '602-555-5555 ', '623-555-5555 ', '2006-09-05 08:51:02.629335');
INSERT INTO client VALUES (12, 87, 2, 1, 1, 1, true, 'Client Name', '', '', '', '', '', '-1', ' ', ' ', ' ', ' \
', '2006-09-18 12:23:49.57459');
Notice that tables bureau and client have entity_id_fk which I am removing out of current db structure therefore thos fields can be taken out.
Thanks.