Andy,

I really appreciate both you and Pat taking the time to walk me through
the basics of CSDP. It's been interesting to see the two different
approaches that have been suggested. The concept of subtypes makes a lot
of sense to me.

In fact, trying to put these tips together into a cohesive conceptual
model has made me realize that I have a much more complex model than I
had initially imagined! Writing the examples was particularly helpful in
this regard. It reminded me of the practice of writing pseudo-code
before actually trying to write production code.

In the model presented below, I have used Pat's idea of overrides for
OptionSettings and your idea of subtypes for Record objects. I have
tried to follow your advice of avoiding equivocation. It's tough, esp
when dealing with a model with which you are not familiar!

The major problem that I'm seeing right now with this model is that I
have to query the database several times if an OptionSetting is not set
for a Record. That could simply be due to my lack of advanced SQL
knowledge. Any advice would be appreciated.

Fact Examples:
"Print Statement Heading" has an agency and client scope
User of group 'system_admin' sets "Print Statement Heading" to true
User of group 'agency' sets "Print Statement Heading" for Client(BigCo
Client1) to true
"Print Statement Heading" for Application is true
"Print Statement Heading" for Agency(BigCo) is false
"Print Statement Heading" for Client(BigCo Client1) is true
"Max Days to Bump Rev" has an agency and client scope
"Max Days to Bump Rev" for Application is 30
"Max Days to Bump Rev" for Agency(BigCo) is 15
"Max Days to Bump Rev" for Client(SmallCo Client1) is 25
"Assign Debtor Number" has an agency scope
"Assign Debtor Number" for Agency(BigCo) is true

-- ERROR: This option cannot be set for a Collector Record--
"Print Statement Heading" for Collector(BigCo Collector1) is false
-- ERROR: This option cannot be set for a Client Record--
"Assign Debtor Number" for Client(BigCo Client1) is 15
-- ERROR: Global options cannot be set by a users of any group except
system_admin--
User of group 'agency' sets "Print Statement Heading" to true

Conceptual scheme:
Reference schemes: Record(Id), ApplicationSetting(Code), OptionName(Name),
OptionSetting(Code), AllowedSettingScope(Code), User(Id)
Base fact types:
F1 Record belongs to a RecordType { 'agency', 'client',
'collector', 'debtor' }
F2 Record belongs to Record
F3 Record overrides ApplicationSetting as OptionSetting
F4 ApplicationSetting has AllowedSettingScope { 'agency',
'client', 'collector', 'debtor' }
F5 ApplicationSetting has global- OptionSetting
F6 User belongs to a Record
F7 User has Group {'system_admin', 'agency_admin',
'agency_representative', 'client_admin', 'client_representative', 'collector'},
Constraints:
C1 Each ApplicationSetting has a global OptionSetting
C2 A Record overrides at most one ApplicationSetting for
each OptionSetting
C3 User of group 'admin' can set ApplicationSetting that
has AllowedSettingScope 'application'
C4 User of group 'agency' can override an
ApplicationSetting that has AllowedSettingScope 'agency'
C5 User of group 'client' can override an
ApplicationSetting that has AllowedSettingScope 'client'
C6 User of group 'collector' can override an
ApplicationSetting that has AllowedSettingScope 'collector'
C7 No record is a parent of itself
Derivation Rules:
D1 Record is a ClientRecord if it has RecordType 'client'
and belongs to Record with RecordType 'agency'
D2 Record is a CollectorRecord if it has RecordType
'collector' and belongs to Record with RecordType 'agency'
D3 Record is a DebtorRecord if it has RecordType 'debtor'
and belongs to Record with RecordType 'client'


Now, where this exercise begins to bear fruit is in turning it into a
database schema. The following is how I'm envisioning this structure to
be implemented (I don't have Visio to do this for me automatically). It
seems to be that the logical model for Record has more facts in it than
I'm showing in my conceptual schema. What do you think?

Logical Data Model (using pseudo-SQL statements):

table Record (
record_id int primary key,
short_name varchar(10),
parent_record_id varchar(50) references Record,
name varchar(50),
record_type {'admin', 'agency', 'client', 'collector', 'debtor'},
);
Ex: Rec_id Short Parent Name Type
1, "Admin", '', "Enoch Root", "admin"
2, "BC", '', "BigCo", "agency"
3, "SC", '', "SmallCo", "agency"
4, "S100", 2, "BigCo Client1", "client"
5, "S200", 2, "BigCo Client2", "client"
6, "S100", 3, "SmallCo Client1", "client"
7, "C1", 2, "BigCo Collector1", "collector"
8, "C2", 2, "BigCo Collector2", "collector"
9, "C1", 3, "SmallCo Collector1", "collector"
10, "100", 4, "BigCo Client1's Debtor1", "debtor"
11, "101", 5, "BigCo Client2's Debtor2", "debtor"
12, "1000", 6, "SmallCo Client1's Debtor1", "debtor"

table ApplicationSetting (
application_setting_id int primary key,
option_name varchar(50),
option_setting varchar(255),
setting_scope {'application', 'agency', 'client', 'collector', 'debtor'},
);
Ex: 1, "Print Statement Heading", "true", "application,agency,client"
2, "Max Days to Bump Rev", "30", "application,agency,client"
3, "Assign Debtor Number", "false", "agency,client"

table ApplicationSettingOverride (
application_setting_override_id int primary key,
application_setting_id int references ApplicationSetting,
record_id varchar(50) references Record,
option_setting varchar(255),
);
Ex: 1, 1, 2, "false"
2, 1, 4, "true"
3, 2, 2, "15"
4, 2, 3, "25"
5, 3, 2, "true"

SQL Examples:
Retrieve the "Print Statement Heading" value for Record BigCo---
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=2;
Retrieve the "Print Statement Heading" value for Record "BigCo Client1"---
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=4;
Retrieve the "Print Statement Heading" value for Record "BigCo Client2"
[first query is empty]---
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=5;
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=2;
Retrieve the "Print Statement Heading" value for Record "BigCo Client2's
Debtor1" [first and second query is empty]---
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=12;
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=5;
SELECT option_setting FROM ApplicationSetting WHERE
application_setting_id=1;
Retrieve the "Print Statement Heading" value for Record SmallCo [first query
is empty]---
SELECT option_setting FROM ApplicationSettingOverride WHERE
application_setting_id=1 AND record_id=3;
SELECT option_setting FROM ApplicationSetting WHERE
application_setting_id=1;

table User (
user_id varchar(50),
record_id int references Record,
group {'system_admin', 'agency_admin', 'agency_representative',
'client_admin', 'client_representative', 'collector'},
);
multi-column primary key on user_id and record_id
Ex: "root", 1, system_admin
"Admin", 2, agency_admin
"Rep", 2, agency_representative
"Admin", 1, client_admin
"Rep", 1, client_representative
"C1", 1, collector
"C1", 2, collector






Thanks,
William

--
Knowmad Services Inc.
http://www.knowmad.com