BorlandTalk.com

Aaron Hallberg · Guest

I am trying to figure out the best way to set up a database to deal with
sibling classes, and I'm hoping that this is an issue that others have
successfully dealt with in the past. Here's a simple example:

TBaseClass = class(TObject)
ID: Integer;
Name: string;
end;

TStringChild = class(TBaseClass)
TheString: string;
end;

TIntegerChild = class(TBaseClass)
TheInteger: Integer;
end;

I don't particularly want to create one table which stores records for both
of the child types, as then for any given record one of the fields will be
null.

CREATE TABLE AllChildren
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name varchar(255) NOT NULL,
TheString varchar(255) NULL,
TheInteger int NULL
)

As such, I was thinking that I would do something like the following
instead:

CREATE TABLE BaseClass
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name varchar(255) NOT NULL
)

CREATE TABLE StringChildDetails
(
ID int NOT NULL
REFERENCES BaseClass(ID),
TheString varchar(255) NOT NULL
)

CREATE TABLE IntegerChildDetails
(
ID int NOT NULL
REFERENCES BaseClass(ID),
TheInteger int NOT NULL
)

This introduces plenty of problems of its own, however - there are
referential integrity issues to deal with, more complicated queries to be
written, and so forth.

Any suggestions?

Thanks in advance,

Aaron Hallberg

Bojidar Alexandrov · Guest

The regular way is to have Type collumn and make joins based on that.
something like

Select BaseClass.Name, Cast(StringChildDetails.TheString as Varchar(255)) as
Value
from BaseClass
inner join StringChildDetails on StringChildDetails.id = BaseClass.id and
BaseClass.type=1
UNION ALL
Select BaseClass.Name, Cast(IntegerChildDetails.TheInteger as Varchar(255))
as Value
from BaseClass
inner join IntegerChildDetails on IntegerChildDetails.id = BaseClass.id and
BaseClass.type=2

The typical example for this is table - person and siblings employee and
customer.

Here is in your case.

CREATE TABLE BaseClass
(
ID int IDENTITY(1,1) PRIMARY KEY,
Name varchar(255) NOT NULL,
Type int
)

CREATE TABLE StringChildDetails
(
ID int PRIMARY KEY
REFERENCES BaseClass(ID),
TheString varchar(255) NOT NULL
)

CREATE TABLE IntegerChildDetails
(
ID int PRIMARY KEY
REFERENCES BaseClass(ID),
TheInteger int NOT NULL
)

Bojidar Alexandrov