Class hierarchy to manage complex relations(quite long)

[Andrea Raimondi]

Hello.

I have this scenario: a bank group needs to have a kiosk with an application
showing several informations.
While the app isn't being navigated, the kiosk has to show(directly inside
the app) several bank advertisements, such as small movies,
pdf files, images, etc.

The problem is that marketing guys want to contextualize what is shown
according to the branch or agency the (perspective) customer is in.
That means that some advertisements are global, others can be local to a
group of agencies/subsidiaries or to a single one.
The backend is already formed and I have a table with these fields:

ID - uniqueidentifier
FileName - the filename to show
MediaType - int - the type of file
COD_FILIALE - string - the branch/agency/subsidiary/etc code. This column
has the value 'NULL'(notice the quotes) when it's a global
resource to be displayed.

In all this, the application has to be user-configurable, this means that
all the media files management has to be done via SQL-queries,
i.e. our company doesn't manage it but the customer does.

This is my branch hierarchy:

Type

TBranchBase = class
public
constructor Create;virtual;
end;

TBranchData = class( TBranchBase )
private
FBranchCode: String;
procedure SetBranchCode(const Value: String);
protected
function GetSQLFilter : String;virtual;
property BranchCode : String read FBranchCode write SetBranchCode;
public
property SQLValue : String read GetSQLFilter;
end;

TAllBranches = class( TBranchData )
public
constructor Create;override;
end;

TBranch = class( TBranchData )
public
property BranchCode;
end;

what I need now is a data access layer, something like this:

Type

TDesignItemBase = class
public
constructor Create;virtual;
function InsertSQL : String;virtual;abstract;
function UpdateSQL : String;virtual;abstract;
end;

TDesignItem = class( TDesignItemBase )
private
FItem : TItem;
FBranchList : TBranchList;
public
constructor Create;override;
destructor Destroy;override;
property Branches : TBranchList;// I have to do this class but I think
you get what I mean
property FileItem : TItem;// Ok, the name is far from being perfect :P
function InsertSQL : String;override;
function UpdateSQL : String;override;
end;

TDesignItemList = class( TDesignItemBase )
public
constructor Create;override;
destructor Destroy;override;
function InsertSQL : String;override;
function UpdateSQL : String;override;
property Items[ Index : Integer ] : TDesignItem;
end;

TAllDesignItems = class( TDesignItemBase )
public
function SQL : String;
property DesignItems[ Index : Integer ] : TDesignItemList;
end;

Apart from the SQL considerations( I would use an OPF if I could, but I can
not for a variety of reasons...) I'd like to know if
there's any pattern/scheme that may help me in crafting this hierarchy which
is not very simple... :-P

The idea is that I'm trying to automate just about everything I can, so that
in the end my transaction will look something like this:
try
ADOConn.BeginTrans;
ADOConn.Execute( AllItems.SQL );
ADOConn.CommitTrans;
except
on E: Exception do
begin
ADOConn.RollbackTrans;
raise;
end;
end,

Hence, all updates and inserts should be generated on the fly, I know it's a
very complex subject but I guess I'll find here knowledgable enough people
being able to pinpoint it.

Any suggestions?

Andrew

P.S. Meanwhile I'll try and do something, to see what gets out.