VB Forum / Database Access / July 2005

hi Geoff,

the relational model does not imply this model.. and implemets this design
using related tables where the detail table references the master table in
it's primary key, in a one to many design... that's to say
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.Master (
ID int PRIMARY KEY
)
CREATE TABLE dbo.Detail (
ID int PRIMARY KEY ,
IdParent int NOT NULL
 CONSTRAINT fk_Master_detail
  FOREIGN KEY
  REFERENCES dbo.Master (ID),
Data varchar(5)
)
INSERT INTO dbo.Master VALUES ( 0 )
INSERT INTO dbo.Master VALUES ( 1 )
INSERT INTO dbo.Master VALUES ( 2 )

INSERT INTO dbo.Detail VALUES ( 1 , 0 ,'a' )
INSERT INTO dbo.Detail VALUES ( 2 , 0 ,'b' )
INSERT INTO dbo.Detail VALUES ( 3 , 0 ,'c' )

INSERT INTO dbo.Detail VALUES ( 4 , 1 ,'a' )
INSERT INTO dbo.Detail VALUES ( 5 , 1 ,'b' )

INSERT INTO dbo.Detail VALUES ( 6 , 2 ,'a' )

SELECT m.ID , d.ID, d.Data
FROM dbo.Master m
 JOIN dbo.Detail d
 ON d.IdParent = m.ID

DROP TABLE dbo.Detail , dbo.Master

an attribute can not be of a table type, as the desing would suffer from not
adhering to properties of a tuple, where every tuple contains exactely 1
value for each of it's attributes and also would violate normal forms
design..
the upper normalization can be however rebuilt in your design using the Data
Shaping Service for OLE DB, which supports hyearachical rowsets in VB6
and/or COM implementation,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtsptas
ks_8jqh.asp ,
where the .Net framework include a new model, the DataSet, supporting
DataTable bound in DataRelation as well...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlr
fSystemDataDataSetClassTopic.asp