Board index » delphi » Object design vs Database Design

Object design vs Database Design

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

 

Re:Object design vs Database Design


http://www.ijack.net/lighthouse.pdf

See if that makes sense.  Post back here with questions.

Eric

Other Threads