Hello there,

I'm looking for information on how to solve a particular design issue. I'll do my best to explain the issue clearly. I have not been able to find anything through the forum search or google, but that may be because I'm looking for the wrong things. I am by no means a database guru, so any additional pointers would be swell.

I guess I should start by explaining what the current application does. I'm tasked with writing a new application and database design with similar functionality.

The application simply manages fields and forms those fields appear on. Each field has a set of properties (name, description, min length, max length, and display size for example). Those properties can be overriden at different levels (of which my application must also manage). These levels are Base (default field properties), Interface, System, Field Set, Form, and Instance.

  • Base (Fields) - This is a list of all fields and their default properties.
  • Interface - An Interface has Systems. Field properties can be overridden here.
  • System - A system has Field Sets. Field properties can be overridden here.
  • Field Set - A field set has forms and fields. Field properties for this Field Set's fields can be overriden and the field may appear multiple times. Each multiple is assigned an instance number.
  • Form - A form has fields from the parent field sets's fields. These field properties can be overriden here.
  • Instance - An instance is a particular instance of the same field on a form (i.e. Name appears twice, the first Name is Instance 1 and the second Name is Instance 2). That instance's field properties can be overriden here.


When the application attempts to "compile" a form, it looks for the field properties to use. If there is no definition at the Instance, level, it looks for a field at the Form Level. If there is no properties defined, it looks in the Field Set level, and so on up the chain until it reaches the Base. Field properties are carried over completely from one level to another. In other words, if field properties are defined at the Form level, the process can stop looking as all the properties are defined there (they are copied from one of the parent levels and then altered as needed).

Here are the table structures and sample data for what I have so far. There are no indexes, yet. Foreign keys are <table>_id columns.

Code:
set nocount on 
GO

if exists(select * from sysobjects where name = 'interface') drop table interface
create table interface (
    id bigint identity(1, 1) not null primary key, 
    name varchar(64) not null
)
GO

insert into interface(name) values('Interface')
GO

if exists(select * from sysobjects where name = 'system') drop table system
create table system (
    id bigint identity(1, 1) not null primary key,
    interface_id bigint not null,
    name varchar(64) not null
)
GO

insert into system(interface_id, name) values(1, 'System')
GO

if exists(select * from sysobjects where name = 'field_set') drop table field_set
create table field_set (
    id bigint identity(1, 1) not null primary key,
    system_id bigint not null,
    name varchar(64) not null,
)
GO

insert into field_set (system_id, name) values(1, 'Field Set')
GO

if exists(select * from sysobjects where name = 'field') drop table field
create table field (    -- Defines "Base" level
    id bigint identity(1, 1) not null primary key,
    name varchar(64) not null,
    description varchar(255) not null default(''),
    minlength int not null default(0),
    maxlength int not null default(0),
    displaySize int not null default(0)
)
GO

insert into field (name, minlength, maxlength, displaySize) 
    select 'Name', 0, 35, 35 union all
    select 'Address', 0, 35, 35 union all
    select 'City', 0, 64, 35 union all
    select 'State', 2, 2, 2 union all
    select 'Zip Code', 4, 4, 4
GO

if exists(select * from sysobjects where name = 'field_set_field') drop table field_set_field
create table field_set_field (
    id bigint identity(1, 1) not null primary key,
    field_set_id bigint not null,
    field_id bigint not null,
    instance int not null default(1)
)
GO

insert into field_set_field (field_set_id, field_id, instance)
    select 1, 1, 1 union all    -- Name
    select 1, 1, 2 union all    -- Name
    select 1, 3, 1 union all    -- City
    select 1, 4, 1              -- State
GO

if exists(select * from sysobjects where name = 'form') drop table form
create table form (
    id bigint identity(1, 1) not null primary key,
    name varchar(64) not null
)
GO

insert into form (name) values('Form 1')
GO


if exists(select * from sysobjects where name = 'form_field') drop table form_field
create table form_field (
    id bigint identity(1, 1) not null primary key,
    form_id bigint not null,
    field_set_field_id bigint not null,
    [order] int not null,
)
GO

insert into form_field (form_id, field_set_field_id, [order])
    select 1, 1, 1 union all    -- Name
    select 1, 2, 2 union all    -- Name
    select 1, 3, 3 union all    -- City
    select 1, 4, 4              -- State
GO

if exists(select * from sysobjects where name = 'field_override') drop table field_override
create table field_override(
    id bigint identity(1, 1) not null primary key,
    field_id bigint not null,            -- Must specify field id
    interface_id bigint not null,        -- Must specify interface at least
    system_id bigint,                    -- Is this override at a system level?
    field_set_id bigint,                 -- Is this override at a field_set level?
    form_id bigint,                      -- Is this override at a form level?
    instance int,                        -- Is this a field instance (must also define form_id)
    -- If a "level id" column is set, all previous column ids must also be set (i.e. if you have field_set_id, 
    -- you must also have system_id and interface_id).  These ids must match the defined relationships in the 
    -- interface, system, field_set, and form tables.
    [level] char(1) not null,            -- 'I' (Instance), 'S' (System), 'L' (Field Set), 'F' (Form), or 'I' (Instance)
    name varchar(64) not null,
    description varchar(255) not null default(''),
    minlength int not null default(0),
    maxlength int not null default(0),
    displaySize int not null default(0)
)
GO

insert into field_override (field_id, interface_id, system_id, field_set_id, form_id, instance, [level], name, description, minlength, maxlength, displaySize) 
    select 1, 1, NULL, NULL, NULL, NULL, 'I', 'Name', 'This is the name field in Interface.', 8, 128, 20 union all
    select 2, 1, 1, NULL, NULL, NULL, 'S', 'Address', 'This is the address field in System.', 0, 35, 35 union all
    select 3, 1, 1, 1, 1, NULL, 'F', 'City', 'This is the city field on Form 1.', 0, 16, 16 union all
    select 1, 1, 1, 1, 1, 2, 'I', 'Name', 'This is the second name field on Form 1.', 0, 128, 20
GO

set nocount off
GO
Performance isn't overly important as a small number of users will be used internally by only a few people. I'm looking for maintainability and ease of querying.

I'm trying to come up with a way to get SQL Server to tell me what the fields look like at various levels. For example, "Show me all the fields as they would look in interface 'i'" or, most importantly, "Show me what Form 'f' is supposed to look like." I've tried various queries, but I haven't had much success. Unfortunately I do not have those anymore as I saw they weren't working out.

For example, for the "Show me form 1" query, I'd like the results:

Code:
form_id  field_id  field_set_field_id  instance    order    name    description                               minlength  maxlength  displaySize
===================================================================================================================================================
1        1         1                   1           1        Name    This is the name field in Interface.      8          128        20
1        1         2                   2           2        Name    This is the second name field on Form 1.  0          128        20
1        3         3                   1           3        City    This is the city field on Form 1.         0          16         16
1        4         4                   1           4        State                                             2          2          2
I think that's everything. The final question is, I guess, are there better ways of storing/accessing this information in the database, and is it possible to develop the views I'd like?

Thank you very much for your time.
- Josh R.