• Here is one way. Create a "master" shape table to reference each individual shape and a separate shape table for each shape. This is done in a way so that only valid shapes may be created and the individual tables must refer to the master entry defined of it. It's not really all that difficult.

    create table Shape(

    ShapeID Int not null Identity( 1, 1 ),

    ShapeType Char( 2 ) not null,

    Origin PointType,

    Diminsiona TinyInt, -- 1, 2 or 3

    Other1 ...,

    Other2 ...,

    Constraint UQ_Shape Primary Key NonClustered(

    ShapeID

    ),

    Constraint CK_ShapeType Check )ShapeType in (

    'LN', -- Line or curve

    'SP', -- Spline

    'PG', -- Polygon( triangle, rectangle, any n-sided shape)

    'EL', -- Ellipse( any regular curved shape)

    'CY', -- Cylinder

    'CN', -- Cone

    'CU', -- Cube

    'BL' -- Amorphous blob

    ))

    );

    go

    create unique clustered index UQ_Shape

    on Shape(

    ShapeID, -- That's right, include the identity field

    ShapeType

    );

    go

    -- Now a table and view for every type of shape

    -- Lines

    create table Line(

    LineID int not null,

    ShapeType Char( 2 ) not null,

    Length Double,

    ...,

    Constraint PK_Line Primary Key Clustered(

    LineID,

    ShapeType

    ),

    Constraint CK_LineType Check (ShapeType = 'LN'), -- Force only lines in this table

    Constraint FK_LineShape Foreign Key (LineID, ShapeType) -- Force it must be a defined shape

    references Shape( ShapeID, ShapeType )

    );

    go

    create view LineShape as

    select s.ShapeID, s.Origin, ..., l.Length,...

    from Shape s

    join Line l

    on s.ShapeID = l.ShapeID

    and s.ShapeType = l.ShapeType

    go

    -- Ellipsi

    create table Ellipse(

    EllipseID int not null,

    ShapeType Char( 2 ) not null,

    Locus PointType not null, -- Must have at least one (circle)

    Locus2 PointType

    ...,

    Constraint PK_Ellipse Primary Key Clustered(

    EllipseID,

    ShapeType

    ),

    Constraint CK_LineType Check (ShapeType = 'EL'), -- Force only ellipsi in this table

    Constraint FK_LineShape Foreign Key (LineID, ShapeType) -- Force it must be a defined shape

    references Shape( ShapeID, ShapeType )

    );

    create view EllipseType....

    The master table provides a "shape" entity so that other tables can maintain a reference to any shape. Notice that if an entry with ID 17 in the master shape table (Shape) is defined as a "line", then there can only be an entry in the Line table with an identifier value of 17. Thus, there cannot be, say, a Square entity with the ID value of 17. There cannot be an entry in any individual shape table without a entry already in the master table and there cannot be more than one entry in a shape table referring to the same master entry. This enforces the 1:1 relationship between the master table and the multiple other tables.

    This technique requires a bit of up front design and analysis. But if you make the effort (each view also requires the proper trigger), you will be able to maintain exactly the data you need for each shape -- and you will find it surprisingly robust, scalable and yet easy to maintain.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms