Hi,
This is more database design question but it relates to object design and I
need help from some real gurus so I hope I'm not out of place in this group.
I have a document producer that basically takes fields from an XML document
and merges values into an MS Word Document. I am using Delphi 5 with an
MS-Access Database(It was a requirement) via ADO but I don't think that
matters. This is a generic design problem.
I have an existing object model like so(I have stripped some non relevant
properties and methods):
//Note: TDBObject keeps DB Info(Table ID, record state, etc.)
TfaXMLSchema = class(TDBObject)
private
fXMLSchemaName: String;
protected
public
Constructor Create(Collection: TCollection); override;
destructor Destroy; override;
property XMLSchemaName: String read fXMLSchemaName write fXMLSchemaName;
...
end;
TfaXMLSchemas = class(TCollection)
private
fDBObject: TDBObject;
function GetItem(Index: Integer): TfaXMLSchema;
procedure SetItem(Index: Integer; const Value: TfaXMLSchema);
protected
public
constructor Create(DBObject: TDBObject);
destructor Destroy; override;
property DBObject: TDBObject read fDBObject;
function Add: TfaXMLSchema;
procedure Delete(Index: integer);
property Items[Index: Integer]: TfaXMLSchema read GetItem write SetItem;
default;
procedure LoadXMLSchemas(AConnection: TADOConnection);
...
end;
TfaOrderField = class(TDBObject)
private
fFieldName: String;
fXMLSchemas: TfaXMLSchemas;
protected
public
constructor Create(Collection: TCollection); override;
destructor Destroy; override;
property FieldName: String read fFieldName write fFieldName;
property XMLSchemas: TfaXMLSchemas read fXMLSchemas write fXMLSchemas;
procedure AddToDB(AConnection: TADOConnection);
procedure UpdateToDB(AConnection: TADOConnection);
procedure DeleteFromDB(AConnection: TADOConnection);
end;
TfaOrderFields = class(TCollection)
private
fDBObject: TDBObject;
fDeleted: TStringList;
function GetItem(Index: Integer): TfaOrderField;
procedure SetItem(Index: Integer; const Value: TfaOrderField);
protected
public
constructor Create(DBObject: TDBObject);
destructor Destroy; override;
property DBObject: TDBObject read fDBObject;
function Add: TfaOrderField;
procedure Delete(Index: integer);
property Items[Index: Integer]: TfaOrderField read GetItem write
SetItem; default;
procedure Assign(Source: TPersistent); override;
procedure LoadOrderFields(AConnection: TADOConnection);
published
end;
So my Database is simple:
TABLE: OrderFields
FIELD: ID INTEGER UNIQUE PRIMARY KEY
FIELD: FieldName STRING
TABLE XMLSchemas
FIELD: ID INTEGER UNIQUE PRIMARY KEY
FIELD: FieldID: INTEGER (Relates to OrderFields.ID)
FIELD: XMLSchemaName: String
Now I want to expand it so that a TfaXMLSchema can contain other
TfaXMLSchemas. The Class design is simple. Just add a TfaXMLSchemas
Collection to TfaXMLSchema. My question is:
How do I design the relationships in the Tables? Can I have a dual/recursive
relationship between XMLSchemas.FieldID and XMLSchemas.ID keeping in mind
that I already have a relationship between XMLSchemas.FieldID and
OrderFields.ID? Do I need a separate Table to link the recursive
relationship and leave the other one alone.
Any help will be greatly appreciated as I have searched all over and have
not found a solution to this design problem.
Thank you,
Gregg
