I'm wondering if anyone is using or thinking of using a what I call table inheritance to create their entities/tables. What I mean by this is using tables that have a one-to-one relationship to create an inheritance chain.

In the system I'm building a small example would be the following abstract classes.

AbstractProduct AbstractPart AbstractFrame

I then have the concrete classes inherit the chain above.

RoadFrame MountainFrame

Does this make sense so far.

Now to store these entities in the database I create a table for each one of the classes above.

AbstractProducts AbstractParts AbstractFrames RoadFrames MountainFrames

Now the most obvious problem I see with this is that the query to retrieve either Road Frames or Mountain Frames would require four joins. So I worry about performance a bit. A second obvious problem to this is that LLBLGen does not currently support inheritance yet. Though I've been told that table inheritance will be added in the future.

My primary reason for building the system in this way is because I have 30+ entities that are very different from each other in their attributes. So I think storing each entity in its own table is a better way to go.

Does anyone have any comments on this? Is this a good/bad way of thinking about building my system?

What are the Primary key columns in these tables:

AbstractProducts AbstractParts AbstractFrames RoadFrames MountainFrames

Thanks.

What are the Primary key columns in these tables:

AbstractProducts AbstractParts AbstractFrames RoadFrames MountainFrames

Thanks.

The primary keys would be as follows.

CREATE TABLE AbstractProducts (
    ProductID int IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE AbstractParts (
    PartID int NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE AbstractFrames (
    FrameID int NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE RoadFrames (
    RoadFrameID int NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE MountainFrames (
    MountainFrameID int NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [AbstractProducts] ADD CONSTRAINT PK_ProductID
PRIMARY KEY NONCLUSTERED ([ProductID])
GO

ALTER TABLE [AbstractParts] ADD CONSTRAINT PK_PartID
PRIMARY KEY NONCLUSTERED ([PartID])
GO

ALTER TABLE [AbstractFrames] ADD CONSTRAINT PK_FrameID
PRIMARY KEY NONCLUSTERED ([FrameID])
GO

ALTER TABLE [RoadFrames] ADD CONSTRAINT PK_RoadFrameID
PRIMARY KEY NONCLUSTERED ([RoadFrameID]) 
GO

ALTER TABLE [MountainFrameID] ADD CONSTRAINT PK_MountainFrameID
PRIMARY KEY NONCLUSTERED ([MountainFrameID])
GO

ALTER TABLE [AbstractParts] ADD CONSTRAINT FK_AbstractParts_AbstractProducts
FOREIGN KEY ([PartID]) REFERENCES [AbstractProducts] ([ProductID])
GO

ALTER TABLE [AbstractFrames] ADD CONSTRAINT FK_AbstractFrames_AbstractParts
FOREIGN KEY ([FrameID]) REFERENCES [AbstractParts] ([PartID])
GO

ALTER TABLE [RoadFrames] ADD CONSTRAINT FK_RoadFrames_AbstractFrames
FOREIGN KEY ([RoadFrameID]) REFERENCES [AbstractFrames] ([FrameID])
GO

ALTER TABLE [MountainFrames] ADD CONSTRAINT FK_MountainFrames_AbstractFrames
FOREIGN KEY ([MountainFrameID]) REFERENCES [AbstractFrames] ([FrameID])
GO

I believe the above code is correct. One thing I'm not sure about is if I should make all the foreign key point back to the AbstractProducts.ProductID field or keep them the way I wrote it above.

Aaron

On a side not here, I'm thinking of merging the AbstractParts table into the AbstractProducts table. This would eliminate some of the work, maintainance and performance problems I see having.